CODEWITHSUNDEEP
excel
Adam
Adam

Reputation: 103

Simple Excel Lookup not returning correct answer

       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

Upvotes: 0

Views: 769

Answers (2)

Michal
Michal

Reputation: 5848

This is a great example of why you should not use VLOOKUP ever again. Start using INDEX/MATCH and a lot of your problems will disappear. Reading about why VLOOKUP sucks will change your life forever.

enter image description here

Upvotes: 1

QHarr
QHarr

Reputation: 84465

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)),"")

Upvotes: 1

Related Questions