#n/a result for vlookup

[adimar]

In the data below I attempt to find items in column C and return date in
column B
using =VLOOKUP(C2, A9:C11, 2, FALSE)

A B C
1 NMR-2438 2/6/08 21098
2 NMR-2439 2/13/08 D817
3 NMR-2467 2/15/08 070321E

Problem is that column C type is 1 (number) or 2 (text).
I need then to convert C data to text, like C2&"", or text(C2, "").
But then vlookup needs to look in column C and covert at the time of search.

Is there a way to write the vlookup to do both value to lookup and range
value conversion to text?

Thank you.

 

[PCLIVE]

Try using:

TEXT(C2,"#")

HTH,
Paul

 

[PCLIVE]

You're using VLOOKUP which searches for a value in the first column of your
specified table and then returns a value in the specified colunm of that
table.

I think you want to use LOOKUP instead.
Try this:

=LOOKUP(C2,C9:C11,B9:B11)

HTH,
Paul

--

 

[adimar]

I copied all data from the text below, imported to new .xls, converted text
to columns, re-typed all vlookup flavors and lookup version and...

The "#" doesn't work...

The lookup version fails on data 070321E (C3).

Thanks.

 

[PCLIVE]

This is because the lookup range needs to be in ascending order.

Try this alternative:
=INDIRECT("B" & MATCH(C1,C9:C11,0)+8)



--

 

[adimar]

This one work. Thank you.

This is because the lookup range needs to be in ascending order.

Try this alternative:
=INDIRECT("B" & MATCH(C1,C9:C11,0)+8)