VLOOKUP source values are a formula result. Getting #N/A

[Pete]

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

 

[isabelle]

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 :

 

[Pete]

We like shopping here. Thank you isabelle. Merci beaucoup!
Pierre

 

[isabelle]

glad to help, merci pour le retour!

--
isabelle



Le 2012-01-24 19:41, Pete a écrit :

 

[Pete]

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

 

[isabelle]

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 :

 

[Vacuum Sealed]

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,myLookups!$B$2:$B$10))
[M2]=IF(C2="","",LOOKUP(C2,myLookups!$A$2:$B$10,myLookups!$B$2:$B$10))

And finally, in Column D, I placed the following to constenate the 2 values:

=L2 &"/"& M2

HTH
Mick.

 

[Pete]

Thank you so much Isabelle and Mick for your super answers. The table
and it's lookups are working like a champ.

Pete

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,myLookups!$B$2:$B$10))
[M2]=IF(C2="","",LOOKUP(C2,myLookups!$A$2:$B$10,myLookups!$B$2:$B$10))

And finally, in Column D, I placed the following to constenate the 2 values:

=L2 &"/"& M2

HTH
Mick.