20

Here's a simple explanation of what I'm having trouble with.

Column A: List of 2300 order numbers
Column B: Email Address associated with an order number
Column C: List of 100 specific order numbers that I need the email address for

So, I'm looking to search column A for a value that matches C, and return the email address from column B in a new column (D).

The current formula almost works, but instead of returning the email address where A matched C, it returns the email address from the same row.

=IF(ISERROR(MATCH(C2,A:A,0)),B2)

Essentially I just need B2 in the formula above to return the value from the same line that matched.

pnuts
  • 58,317
  • 11
  • 87
  • 139
mn8809
  • 454
  • 1
  • 5
  • 13
  • Related https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Tung Sep 12 '19 at 21:02

3 Answers3

50

I think what you want is something like: 

=INDEX(B:B,MATCH(C2,A:A,0))

I should mention that MATCH checks the position at which the value can be found within A:A (given the 0, or FALSE, parameter, it looks only for an exact match and given its nature, only the first instance found) then INDEX returns the value at that position within B:B.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 2
    In this particular case it might have been easier to use =VLOOKUP(C2,A:B,2,FALSE) but there appear to be strong arguments for using match / index routinely http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/ – Tom Sharpe Oct 15 '14 at 11:54
  • I agree with your line of reasoning @pnuts - maybe I will switch to using index / match in future! – Tom Sharpe Oct 15 '14 at 13:54
  • @pnuts Can you answer this? https://stackoverflow.com/questions/48478990/how-to-get-the-names-of-the-regions-from-maxmind-data – Umair Jan 28 '18 at 12:17
  • This is great - Exactly what I need :) Thank you – Abid Jul 06 '18 at 07:03
3
=IF(ISNA(INDEX(B:B,MATCH(C2,A:A,0))),"",INDEX(B:B,MATCH(C2,A:A,0)))

Will return the answer you want and also remove the #N/A result that would appear if you couldn't find a result due to it not appearing in your lookup list.

Ross

maskacovnik
  • 3,080
  • 5
  • 20
  • 26
Ross
  • 31
  • 1
2

All you have to do is write an IF condition in the column d like this:

=IF(A1=C1;B1;" ")

After that just apply this formula to all rows above that one.

Alexey
  • 144
  • 2
  • 12
  • 1
    I may have done a poor job in my explanation. This would be exactly what I needed if I only needed to pull data from one row, but the columns didn't line up. Thank you though! – mn8809 Oct 15 '14 at 02:28