Lookup problems

[kieranm105]

Hi
I have inserted an Access Query that looks up from our database and puts the restricted data into a new sheet on my Excel file.

On the main Input sheet I have a cell (I21) that I will enter an enquiry number into (e.g. 17000). The formula I have wont work and return #N/A in my cell I want the lookup answer in.
These formulae work in other sheets and if I use =A2 and select the first cell in my query sheet.

=IF(LOOKUP($I$21,Sheet2!$A$2:$A$50000,Sheet2!$B$2:$B$50000)="","",LOOKUP($I$21,Sheet2!$A$2:$A$50000,Sheet2!$B$2:$B$50000))

=INDEX(Sheet2!A2:B5000,MATCH('Input Sheet'!I21,Sheet2!A2:A5000,0),2)

I have sorted (ascending) the data in my 'A' column for the lookup and made the cells numbers (to 0 places).

Any ideas what is wrong?

Kieran

[Jonmo1]

Sounds like maybe I21 is a real number, but Sheet2!$A$2:$A$50000 are 'Numbers stored as text'
Or Vice Versa

Do either of these work? (really only concerned about the basic lookup, not the IF = "" test)

=LOOKUP($I$21&"",Sheet2!$A$2:$A$50000,Sheet2!$B$2:$B$50000)
=LOOKUP($I$21+0,Sheet2!$A$2:$A$50000,Sheet2!$B$2:$B$50000)

[kieranm105]

Thanks Jonmo1 the first formula worked!

[Jonmo1]

You're welcome.

Keep in mind that was really only troubleshooting. And you have what is called a 'Workaround', not a solution.

You'd be better off in the long run correcting the cause of the problem.
Which is that the 'numbers' in Sheet2!$A$2:$A$50000 are not really numbers. They're TEXT strings that look like numbers. AKA 'Number Stored As Text'.

Try copying a blank empty cell.
Highlight that column A on Sheet2
Right Click - Paste Special - Values - Add.

Now your original formula should work as expected.