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

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

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

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

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 -

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