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

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

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

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

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 -

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

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.

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.