Search code examples
excelexcel-formulaexcel-2013vba

Script/macro that searches if the lookup value pair (in column's D,E) exists in lookup array (in column's A,B)

Script/macro that searches if the lookup value pair (in column's C,D) exists in lookup array (in column's A,B).

I came across MATCH VLOOKUP but they look up only one value but what I want is something as follows.

Please note : COLUMN A is always in sorted order

  A          B         C              D         E         F
 _______________________________________________________________________
  BOB        100      details1       CCA       100        print "False"       
  BOB        200      details2       DBA       100        print "False"         
  BOB        300      details3       BOB       100        print "details1"         
  CCA        500      details4       BOB       500        print "False"
Solution

  • As an array formula in F1

    Press CTRL+SHIFT+ENTER together

    =INDEX($C$1:$C$4,MATCH(D1&E1,$A$1:$A$4&$B$1:$B$4,0))