Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP source values are a formula result. Getting #N/A
Using this formula IN B2 to return a value located within a text
string: =IF(A2="","",MID(A2,10,1)) This returns the 10th character. for example: A2 contains TBL18016210 The formula properly returns a 1. I also return the 11th character to C2 (value of 0 in this case, if it exists). Now it gets messy: .. . .I want to do a VLOOKUP in column D to a table on those returned values 1 and 0, and concatenate them with a /(forward slash) between them. Want to return "BRN/BLK" I think the VLOOKUP and CONCATENATE doesn't like the fact that the lookup values are the result of formulas. Thoughts? (Plenty of room for helper columns.) TIA. Pete |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP source values are a formula result. Getting #N/A
hi PeTe,
on sheet table A B --------------- 0 BLK 1 BRN =INDEX(table!B:B,MATCH(B2,table!A:A,0))&" / "&INDEX(table!B:B,MATCH(C2,table!A:A,0)) -- isabelle Le 2012-01-24 17:40, Pete a écrit : Using this formula IN B2 to return a value located within a text string: =IF(A2="","",MID(A2,10,1)) This returns the 10th character. for example: A2 contains TBL18016210 The formula properly returns a 1. I also return the 11th character to C2 (value of 0 in this case, if it exists). Now it gets messy: . . .I want to do a VLOOKUP in column D to a table on those returned values 1 and 0, and concatenate them with a /(forward slash) between them. Want to return "BRN/BLK" I think the VLOOKUP and CONCATENATE doesn't like the fact that the lookup values are the result of formulas. Thoughts? (Plenty of room for helper columns.) TIA. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP source values are a formula result. Getting #N/A
We like shopping here. Thank you isabelle. Merci beaucoup!
Pierre On Jan 24, 5:52*pm, isabelle wrote: hi PeTe, on sheet table A * * * B --------------- 0 * * * BLK 1 * * * BRN =INDEX(table!B:B,MATCH(B2,table!A:A,0))&" / "&INDEX(table!B:B,MATCH(C2,table!A:A,0)) -- isabelle Le 2012-01-24 17:40, Pete a écrit : Using this formula IN B2 to return a value located within a text string: =IF(A2="","",MID(A2,10,1)) This returns the 10th character. for example: A2 contains TBL18016210 The formula properly returns a 1. I also return the 11th character to C2 (value of 0 in this case, if it exists). Now it gets messy: . . .I want to do a VLOOKUP in column D to a table on those returned values 1 and 0, and concatenate them with a /(forward slash) between them. Want to return * * "BRN/BLK" I think the VLOOKUP and CONCATENATE doesn't like the fact that the lookup values are the result of formulas. Thoughts? *(Plenty of room for helper columns.) TIA. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP source values are a formula result. Getting #N/A
glad to help, merci pour le retour!
-- isabelle Le 2012-01-24 19:41, Pete a écrit : We like shopping here. Thank you isabelle. Merci beaucoup! Pierre On Jan 24, 5:52 pm, wrote: hi PeTe, on sheet table A B --------------- 0 BLK 1 BRN =INDEX(table!B:B,MATCH(B2,table!A:A,0))&" /"&INDEX(table!B:B,MATCH(C2,table!A:A,0)) -- isabelle |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP source values are a formula result. Getting #N/A
My excitement is short lived, as it still returns a #N/A value, unless
the lookup value is not the result of this formula. Maybe it's hosing it. . . =IF(A2="","",MID(A2,10,1)) then look for the color in a table. (Trying the INDEX & MATCH piecemeal without the 2nd part of the suggested formula, for excercise purposes.. Thanks for any ideas.. Pete On Jan 24, 6:50*pm, isabelle wrote: glad to help, merci pour le retour! -- isabelle Le 2012-01-24 19:41, Pete a écrit : We like shopping here. *Thank you isabelle. *Merci beaucoup! Pierre On Jan 24, 5:52 pm, *wrote: hi PeTe, on sheet table A * * * B --------------- 0 * * * BLK 1 * * * BRN =INDEX(table!B:B,MATCH(B2,table!A:A,0))&" /"&INDEX(table!B:B,MATCH(C2,table!A:A,0)) -- isabelle- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP source values are a formula result. Getting #N/A
hi Pete,
you can added to the table a new row with in column A formula - ="" and in column B formula - ="" (or text ---) -- isabelle Le 2012-01-25 11:46, Pete a écrit : My excitement is short lived, as it still returns a #N/A value, unless the lookup value is not the result of this formula. Maybe it's hosing it. . . =IF(A2="","",MID(A2,10,1)) then look for the color in a table. (Trying the INDEX& MATCH piecemeal without the 2nd part of the suggested formula, for excercise purposes.. Thanks for any ideas.. Pete |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP source values are a formula result. Getting #N/A
On 26/01/2012 5:01 AM, isabelle wrote:
hi Pete, you can added to the table a new row with in column A formula - ="" and in column B formula - ="" (or text ---) Hi Pete You need to convert your extracted value to a number so change your formula to this: [B2]=VALUE(IF($A2="","",MID($A2,10,1))) [C2]=VALUE(IF($A2="","",MID($A2,11,1))) With the helper columns in mind I did the following ( change Sheet Names, Columns & Ranges to suit) I placed the below formulas in Columns L & M to extract the value from the array. [L2]=IF(B2="","",LOOKUP(B2,myLookups!$A$2:$B$10,myLook ups!$B$2:$B$10)) [M2]=IF(C2="","",LOOKUP(C2,myLookups!$A$2:$B$10,myLook ups!$B$2:$B$10)) And finally, in Column D, I placed the following to constenate the 2 values: =L2 &"/"& M2 HTH Mick. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP source values are a formula result. Getting #N/A
Thank you so much Isabelle and Mick for your super answers. The table
and it's lookups are working like a champ. Pete On Jan 25, 9:10*pm, Vacuum Sealed wrote: On 26/01/2012 5:01 AM, isabelle wrote: hi Pete, you can added to the table a new row with in column A formula - ="" and in column B formula - ="" (or text ---) Hi Pete You need to convert your extracted value to a number so change your formula to this: [B2]=VALUE(IF($A2="","",MID($A2,10,1))) [C2]=VALUE(IF($A2="","",MID($A2,11,1))) With the helper columns in mind I did the following ( change Sheet Names, Columns & Ranges to suit) I placed the below formulas in Columns L & M to extract the value from the array. [L2]=IF(B2="","",LOOKUP(B2,myLookups!$A$2:$B$10,myLook ups!$B$2:$B$10)) [M2]=IF(C2="","",LOOKUP(C2,myLookups!$A$2:$B$10,myLook ups!$B$2:$B$10)) And finally, in Column D, I placed the following to constenate the 2 values: =L2 &"/"& M2 HTH Mick. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use vlookup to return a formula not a result | Excel Worksheet Functions | |||
the Row # from the result of Vlookup formula | Excel Worksheet Functions | |||
Use formula result in vlookup | Excel Discussion (Misc queries) | |||
VLOOKUP FORMULA IS DISPLAYED AS THE RESULT | Excel Discussion (Misc queries) | |||
VLOOKUP result is not showing up - only the formula | Excel Worksheet Functions |