lookup not working

[dougmorgan]

Hi,

I've used lookups for years and have never had a problem,but for some reason the attached file will not return the appropriate cell value.

I've tried reformatting etc with no luck.
I would love to know why this is not working,can anyone help?

Doug

[contaminated]

You values in Column A are mixed: entries are there both text and general formatted, whereas majority entries of column D (C labeled) are general formatted. So, VLOOKUP not able to find text entries within numeric and vise versa. Try to format all your values in lookup column either text or general format

Or use this formula
=INDEX($E$3:$E$30,MATCH(TRUE,INDEX((--$A3=--$D$3:$D$30),0),0))

[zbor]

Your numbers sometimes are stored as text and sometimes as numbers...

This should do it: =VLOOKUP(TEXT(A3,0),TEXT($D$3:$E$3000,0),2,FALSE)

comfirmed with ctrl+shift+enter

[zbor]

or this non-array:

=INDEX(E:E,MATCH(TEXT(A3,0),INDEX(TEXT(D:D,0),0),0))

[contaminated]

dougmorgan
As you noticed both ZBor and me suggested array formulas. Mine just isn't confirmed w/ctrl+shift+enter, but works just like array formula. In zbor's example huge range was used as table array for VLOOKUP function: up to 3000. This makes your workbook heavier and slows down calculation jobs independent from if you use my formula or zbor's

I advise you to reformat ALL you values to GENEAL and never allign text to the left. Numbers are stored at right, text is at the left.

[dougmorgan]

Thanks for your help,all now sorted.

Doug

[peterjuhnke]

Hello dougmorgan
As zbor has said, some of the numbers are stored as numbers and some as text.
Is there a reason for this or can they be convertered to numbers.
By converting to numbers your formula works fine!
To mass convert, find a empty cell, ensure it is formatted as a number, place one (1) in it.
Now copy it and high-light the columns you need to convert, special paste- multiply.
This will convert all to numbers, your formulas work fine.
Hope this helps
Regards
Peter

[martindwilson]

just adding 0 with paste special to both columns will fix it