VLOOKUP works aonly for the first row

[CCNProjects]

Hello,
My VLOOKUP formula ( VLOOKUP(B2,key2,4,FALSE)) works only for the first row, i get "#N/A" for all other rows.

I should get the same outcome for the first 10 rows (same individual), but I get the right result only for the first row.

My identifier column (common column) has been sorted and formatted to text in the two spreadsheets.

Thanks for any hint.

 

[xlbo]

Formatting to text will do nothing to the underlying data

use this formula

=TEXT(cell_ref)

to determine if your data is truly all text

More than likely the 1st row of data is somehow different to the rest - either data type or there may be spaces at the end of the text...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Another possibility: Is the Named Range key2 using absolute referencing, or relative? If you go to Insert > Name > Define and click on key2, there should be dollar signs ($) in the "refers to" box.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.