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.

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.

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.

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.

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.

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.