Vlookup where table array is in text format


J

jodieg

Hello!
I am trying to do a vlookup where the format of the origin cells are
"general" and the format of table array is all in text. I am looking up a
combination of alpha and numeric characters on the origin.
I know I can't look up 2 different formats but want the vlookup formula to
acknowledge the difference.
I tried the following formula but it does not work:
=VLOOKUP(C9,TRIM('[Copy of MP_Book.xls]Merge Purge
Results'!$B$9:$T$286),19,FALSE)
It returns an error of #N/A.
Any suggestions on how to get the table array of text to be matched with the
general value?
Thanks for your help!
 
Ad

Advertisements

C

CLR

If your Table has "spaces" in it's text strings and your origin cell does
not, then maybe try adding a new column to the left of your table and TRIM
the spaces out there, and modify your VLOOKUP to look for the origin in that
new column.

Vaya con Dios,
Chuck, CABGx3
 
J

jodieg

That does work but I was hoping to include it right in the vlookup formula.
Thanks,
Jodie
--
Jodie Gardner


CLR said:
If your Table has "spaces" in it's text strings and your origin cell does
not, then maybe try adding a new column to the left of your table and TRIM
the spaces out there, and modify your VLOOKUP to look for the origin in that
new column.

Vaya con Dios,
Chuck, CABGx3



jodieg said:
Hello!
I am trying to do a vlookup where the format of the origin cells are
"general" and the format of table array is all in text. I am looking up a
combination of alpha and numeric characters on the origin.
I know I can't look up 2 different formats but want the vlookup formula to
acknowledge the difference.
I tried the following formula but it does not work:
=VLOOKUP(C9,TRIM('[Copy of MP_Book.xls]Merge Purge
Results'!$B$9:$T$286),19,FALSE)
It returns an error of #N/A.
Any suggestions on how to get the table array of text to be matched with the
general value?
Thanks for your help!
 
C

CLR

Well, if the spaces are consistant, you might consider something like

=VLOOKUP(LEFT(C9,3&" "&MID(C9,4,3).......or something similar........

Vaya con Dios,
Chuck, CABGx3



jodieg said:
That does work but I was hoping to include it right in the vlookup formula.
Thanks,
Jodie
--
Jodie Gardner


CLR said:
If your Table has "spaces" in it's text strings and your origin cell does
not, then maybe try adding a new column to the left of your table and TRIM
the spaces out there, and modify your VLOOKUP to look for the origin in that
new column.

Vaya con Dios,
Chuck, CABGx3



jodieg said:
Hello!
I am trying to do a vlookup where the format of the origin cells are
"general" and the format of table array is all in text. I am looking up a
combination of alpha and numeric characters on the origin.
I know I can't look up 2 different formats but want the vlookup formula to
acknowledge the difference.
I tried the following formula but it does not work:
=VLOOKUP(C9,TRIM('[Copy of MP_Book.xls]Merge Purge
Results'!$B$9:$T$286),19,FALSE)
It returns an error of #N/A.
Any suggestions on how to get the table array of text to be matched with the
general value?
Thanks for your help!
 
Ad

Advertisements

J

jodieg

Unfortunately, the number of spaces varies depending on the value in that
column.
--
Jodie Gardner


CLR said:
Well, if the spaces are consistant, you might consider something like

=VLOOKUP(LEFT(C9,3&" "&MID(C9,4,3).......or something similar........

Vaya con Dios,
Chuck, CABGx3



jodieg said:
That does work but I was hoping to include it right in the vlookup formula.
Thanks,
Jodie
--
Jodie Gardner


CLR said:
If your Table has "spaces" in it's text strings and your origin cell does
not, then maybe try adding a new column to the left of your table and TRIM
the spaces out there, and modify your VLOOKUP to look for the origin in that
new column.

Vaya con Dios,
Chuck, CABGx3



:

Hello!
I am trying to do a vlookup where the format of the origin cells are
"general" and the format of table array is all in text. I am looking up a
combination of alpha and numeric characters on the origin.
I know I can't look up 2 different formats but want the vlookup formula to
acknowledge the difference.
I tried the following formula but it does not work:
=VLOOKUP(C9,TRIM('[Copy of MP_Book.xls]Merge Purge
Results'!$B$9:$T$286),19,FALSE)
It returns an error of #N/A.
Any suggestions on how to get the table array of text to be matched with the
general value?
Thanks for your help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top