| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup where table array is in text format
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! -- Jodie Gardner |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup where table array is in text format
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" wrote: 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! -- Jodie Gardner |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup where table array is in text format
That does work but I was hoping to include it right in the vlookup formula.
Thanks, Jodie -- Jodie Gardner "CLR" wrote: 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" wrote: 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! -- Jodie Gardner |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup where table array is in text format
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" wrote: That does work but I was hoping to include it right in the vlookup formula. Thanks, Jodie -- Jodie Gardner "CLR" wrote: 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" wrote: 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! -- Jodie Gardner |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup where table array is in text format
Unfortunately, the number of spaces varies depending on the value in that
column. -- Jodie Gardner "CLR" wrote: 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" wrote: That does work but I was hoping to include it right in the vlookup formula. Thanks, Jodie -- Jodie Gardner "CLR" wrote: 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" wrote: 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! -- Jodie Gardner |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| array table and vlookup | Excel Worksheet Functions | |||
| vlookup - can't pick up table array | Excel Worksheet Functions | |||
| Vlookup -Want to make table of array optional | Excel Discussion (Misc queries) | |||
| i want to do a vlookup but i want the table array to be changing | Excel Worksheet Functions | |||
| VLOOKUP - 3 Table Array | Excel Worksheet Functions | |||
Linear Mode
