Lookup not working correctly

Pinchy

New Member
Joined
Oct 19, 2006
Messages
2
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,760
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Sep 30, 2005
Messages
1,218
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

New Member
Joined
Oct 19, 2006
Messages
2
=LOOKUP(C3,CA10:CA165,CB10:CB165)

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

Thanks
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,196
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top