21

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?

Ram
  • 3,092
  • 10
  • 40
  • 56
YelizavetaYR
  • 1,611
  • 6
  • 21
  • 37

1 Answers1

33

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).
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • It worked! =IFERROR(VLOOKUP(D3,List!A:C,3,FALSE),"No Match") I'm not entirely sure i understand the List!A:C (the A:C) part. I thought the function worked as such - find the data in one column and return the contents of another (What if i only want it to search in Column A and always return Column C or D ... but never search in those columns). – YelizavetaYR Apr 14 '14 at 14:04
  • 1
    `List!A:C,3,` means that yor're going to search in first column of range `List!A:C` (i.e. `List!A:A`) and return corresponding value from third column of range `List!A:C` (i.e. `List!C:C`) – Dmitry Pavliv Apr 14 '14 at 14:07
  • Years later, still a fantastic solution. Thanks! – hockey2112 Feb 17 '20 at 22:58