#n/a result for vlookup
 

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.

#n/a result for vlookup
 

Try using:

TEXT(C2,"#")

HTH,
Paul

--

"adimar" wrote in message
...

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.

#n/a result for vlookup
 

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

--

"PCLIVE" wrote in message
...
Try using:

TEXT(C2,"#")

HTH,
Paul

--

"adimar" wrote in message
...

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.

#n/a result for vlookup
 

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" wrote:

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

--

"PCLIVE" wrote in message
...
Try using:

TEXT(C2,"#")

HTH,
Paul

--

"adimar" wrote in message
...

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.

#n/a result for vlookup
 

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

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



--

"adimar" wrote in message
...
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" wrote:

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

--

"PCLIVE" wrote in message
...
Try using:

TEXT(C2,"#")

HTH,
Paul

--

"adimar" wrote in message
...

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.

#n/a result for vlookup
 

This one work. Thank you.

"PCLIVE" wrote:

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

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



--

"adimar" wrote in message
...
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" wrote:

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

--

"PCLIVE" wrote in message
...
Try using:

TEXT(C2,"#")

HTH,
Paul

--

"adimar" wrote in message
...

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.