Simple Excel Lookup not returning correct answer

Question

       A                    B        C                  D
1      listing_region    region_id   region-name        regioncode
2      Auckland             1        Northland          0
3      Waikato/Waitomo      7        Auckland           1
4      Waikato/Waitomo      7        Waikato/Waitomo    2
5      Waikato/Waitomo      7        Bay of Plenty      3
6      Waikato/Waitomo      7        The Lakes District 4
7      Waikato/Waitomo      7        Hawkes Bay         5
8      Waikato/Waitomo      7        Manawatu           6
9      Waikato/Waitomo      7        Taranaki           7
10     Waikato/Waitomo      7        Wairarapa          8
11     Bay of Plenty        1        Wellington         9

Hi there! I am using a very simple Lookup function in excel to match a value in a cell with a column of values and return the corresponding value.

For instance, the formula I used for cell B3 is =LOOKUP(A3,$C$2:$C$11,$D$2:$D$11) And I was expecting the value of D4 be returned, which should be 2. However, I got 7, which corresponds with the region "Taranaki".

I also tried VLOOKUP function and still I got the same result.

Could you please help me on this very simple lookup function?

Thanks in advance.

excel lookup

QHarr QHarr · Accepted Answer · 2018-12-01T04:11:05

Why did you get the wrong answer?

For LOOKUP to work as expected data must be sorted by column C ascending.

You could also use Index and Match:

Columns C and D are your lookup table. You want to match listing_region against region_name (in column C) and retrieve the value from column D in the same row. You can use MATCH to find the row where the match is and INDEX retrieve the matching row number value from column D. Wrap the whole thing in IFERROR in case no match found.

So, in B2 and drag down:

=IFERROR(INDEX($D$2:$D$11,MATCH(A2,$C$2:$C$11,0)),"")

Simple Excel Lookup not returning correct answer

2 Answers