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

  • Thread starter Thread starter Pete
  • Start date Start date
P

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
 
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 :
 
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
 
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 :
 
hi Pete,


you can added to the table a new row
with in column A formula -> =""
and in column B formula -> ="" (or text ---)
Click to expand...
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.
 
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 ---)
Click to expand...

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.
Click to expand...
 
Back
Top