Lookup not working correctly

[Pinchy]

I have a lookup set up to display a number reference against a name that is selectable via validate 'list'.
It all works fine except when one name in the list is selected, it shows the wrong number reference.

Here's 4 lines from the list (which has 150+):

Queen Elizabeth and Queen Mother 27
Queen Elizabeth Hospital, Kings Lyn 66
Queen Elizabeth Hospital, Gateshead 106
Queen Elizabeth Hospital, Woolwich 31

When you select 'Queen Elizabeth Hospital, Gateshead' from the validate list (cell C3), it strangely displays '27' in the cell C4 instead of 106. All other lookups work fine except this one line.

Any ideas?

Many thanks

 

[jimrward]

can you add to your post your lookup statement, are you using VLOOKUP if so look at the last parameter as this governs the quality of the lookup for want of a better word, whether it be exact, near etc

 

[erik.van.geit]

Hi, Pinchy
Welcome to the Board !!!!!

check out the helpfiles for LOOKUP
the datatable needs to be sorted
then it will work correctly

kind regards,
Erik

 

[JazzSP8]

Does the number 27 mean anything on the list? Relate to another hospital?

If so, try making the lookup an exact match by including a ",False" at the end of the formula.

HTH

 

[Pinchy]

=LOOKUP(C3,CA10:CA165,CB10:CB165)

Never used VLOOKUP before but would be interested to understand more.

Thanks

 

[erik.van.geit]

lookup cannot get an extra argument (false or 0)
you would need vlookup

  A                                   B   C  D                                   E     F   
1 Queen Elizabeth and Queen Mother    27                                         wrong ok  
2 Queen Elizabeth Hospital, Kings Lyn 66     Queen Elizabeth Hospital, Gateshead 27    106 
3 Queen Elizabeth Hospital, Gateshead 106                                                  
4 Queen Elizabeth Hospital, Woolwich  31                                                   

bn bn
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
E2    =LOOKUP(D2,A1:A4,B1:B4)
F2    =VLOOKUP(D2,A1:B4,2,0)

[Table-It] version 06 by Erik Van Geit

(as said: sorted table can be handled with lookup)