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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,792
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,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,141,224
Messages
5,705,122
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top