Excel 2007 : Lookup formula not seeing some numbers until they are re-entered

Hello All,

I am in the process of making my first lookup formula worksheet but I have run in to some problems.

The purpose of this formula is to look up people in a particular room number and show them in a shortened list.

This is done to 2 separate lists so I can compare / correct any differences.

Unfortunately the formula does not recognize all instances of a room number in one of the lists.

As you can see on the attached worksheet, only 2 of the 3 people in '134' show up. If I type '134' in to the room number again, the formula sees it, even though I am typing exactly the same thing as what is in there already!! I have checked, there are no spaces or anything, the original number is correct as far as I can see.

Any idea why my lookup formula is failing to see some of these room numbers??


Many thanks,
Garry

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Please don't upload pictures. None of us is inclined to recreate your data when you have a workbook available - see guidelines in the Rules area of this forum.

Sorry, I have amended original post as asked.

As the room numbers come from another, imported worksheet using: e.g. "=Sheet1!A13" I tried using the TRUNC function in case that was the problem after reading this article:

http://support.microsoft.com/kb/315961

However it did not work.

The formula used was =TRUNC(Sheet1!A13,0) to get the Room numbers.

The lookup formula still does not see some numbers.

Hi Cutter,

Thanks very much for the good spots with the row reference errors.

I have utilized your shorter formula, I have no need to be backwards compatible so shorter is better. I have attached the updated worksheet and I will change it in the top post too.

Even with the shorter formula, I still have the annoying problem of some numbers not showing up. For example, typing 124 in the lookup box brings up 1 of 2 people in 124 from My List, and none of the 2 people in the Office's list. I am convinced it is something to do with formats, numbers/numbers as text/etc but after trying everything in this old post (link) I have not found the solution.

The weordest thing is that typing 124 next to each person again (ie typing the same thing over the top) makes them appear. Obviously I don't want to do this every time as there are over 200 people in the master list.

Garry

for some reason some of the numbers in col a are text numbers,so they don't match try=ISNUMBER(A9) it says false
to fix that put 0 in a spare cell copy
select all the numbers in column a
paste special
add
and just in case repeat in column e

also the formula as pointed out seems to have skipped a row drag it down again from j4

id do it like this just hide sheet1

no it counts how may entries for each room countif($a$1:a1,a1) dragged down increments on each room as it finds it
so
200 1
200 2
201 1
200 3
203 1
add the room number to it with a - in the middle (or separator of your choice!)
a1&"-"&countif($a$1:a1,a1)
200-1
200-2
201-1
200-3
203-1
gives an unique reference to match against
so now you can lookup by dragging down
200&"-"&rows($a$1:a1) ie 200-1
200&"-"&rows($a$1:a2) ie 200-2