Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 259
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I use vlookup to return a formula not a result G Sedgwick Excel Worksheet Functions 1 May 5th 09 03:05 PM
the Row # from the result of Vlookup formula Andri Excel Worksheet Functions 4 September 1st 07 09:38 AM
Use formula result in vlookup billinr Excel Discussion (Misc queries) 1 June 6th 07 06:12 PM
VLOOKUP FORMULA IS DISPLAYED AS THE RESULT tony Excel Discussion (Misc queries) 1 August 1st 06 05:39 AM
VLOOKUP result is not showing up - only the formula Linda Excel Worksheet Functions 10 December 21st 05 07:37 AM


All times are GMT +1. The time now is 11:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"