Check if an excel cell exists on another worksheet in a column - and return the contents of a different column

20
votes

What I want to do is to say if the contents of cell D3 (on current worksheet) exist in column A in the first worksheet (in my case entitled list). (and they always do exist somewhere). Return the contents of the corresponding row in Column C.

In other words if the matching cell is found in Row 12 - return data from C12.

I've used the following syntax but I can't seem to get the last part to work correctly. 

=IF(ISERROR(MATCH(D3,List!A:A, 0)), "No Match", VLOOKUP(D3,List!A:A,1,TRUE))

How to fix the formula?

1
excelexcel-formulavlookupexcel-match

1 Answers

29
votes

You can use following formulas.

For Excel 2007 or later:

=IFERROR(VLOOKUP(D3,List!A:C,3,FALSE),"No Match")

For Excel 2003:

=IF(ISERROR(MATCH(D3,List!A:A, 0)), "No Match", VLOOKUP(D3,List!A:C,3,FALSE))

Note, that

  • I'm using List!A:C in VLOOKUP and returns value from column № 3
  • I'm using 4th argument for VLOOKUP equals to FALSE, in that case VLOOKUP will only find an exact match, and the values in the first column of List!A:C do not need to be sorted (opposite to case when you're using TRUE).