Smart VLookup...

My part Number has two "faces" numeric code and Character code
Example
Prod1 is xxxyz numeric say 911-y-z, the y is spec like 10cm,.. and the
z is color So, Item 91113 Ribbon , 10cm ,and yellow.

It also has a name: Ribbon ,10 CM and YELLOW.

If I gave a value of 91100 to Ribbon and 10 to "10 CM" and a 3 to
Yellow then I can transform:
Ribbon 10 CM Yellow to 91100+10+3= 91113

Now the fun part:

I have Column A = Item Description
B = Specs
c = Color

I built 3 Tables to convert Character to Value
So in Column D I have =Vlookup..+VLookup...+VLookup....

The problem is Colum B Table There are Several of them Depending on The
Result of Table A selection

So, back to the example if I have Item 922 I must go to a different
B-Table because it has a DIFFERENT specs say weight

Item 92233 is

Powder, 10 gm , Yellow ( color applies to all items)

Table B2 is different here (it is for Powder)
I have 15gm / 10
23gm/ 20
35gm/ 30

While Table B1 is it is for Ribbons)
10cm/ 10
15cm/ 20
40cm/ 30

So back to the Formula in Colum D THe middle Table must be selected
based on the value in Column A

I have to program this rather than build a complicated
formula.Especially these tables are dynamic. Products can expand with
the consequence of different B tables.

In case you may be confused by my naming
I call the table for column A A-Table and Column B B-Table etc

Appreciate your direction in the matter

>>Appreciate your direction in the matter

Try using the INDIRECT function in your second lookup.

Construct a lookup that returns the name of the B table associated with each
product. Say this lookup returns the name of the B table in cell A1. So A1
will have "Table B1" or "Table B2", etc. Your spec lookup will do something
like this: =VLOOKUP(Spec,INDIRECT(A1),2,False)

--
Jim
"Arishy" <[email protected]> wrote in message
news:[email protected]...
| My part Number has two "faces" numeric code and Character code
| Example
| Prod1 is xxxyz numeric say 911-y-z, the y is spec like 10cm,.. and the
| z is color So, Item 91113 Ribbon , 10cm ,and yellow.
|
| It also has a name: Ribbon ,10 CM and YELLOW.
|
| If I gave a value of 91100 to Ribbon and 10 to "10 CM" and a 3 to
| Yellow then I can transform:
| Ribbon 10 CM Yellow to 91100+10+3= 91113
|
| Now the fun part:
|
| I have Column A = Item Description
| B = Specs
| c = Color
|
| I built 3 Tables to convert Character to Value
| So in Column D I have =Vlookup..+VLookup...+VLookup....
|
| The problem is Colum B Table There are Several of them Depending on The
| Result of Table A selection
|
| So, back to the example if I have Item 922 I must go to a different
| B-Table because it has a DIFFERENT specs say weight
|
| Item 92233 is
|
| Powder, 10 gm , Yellow ( color applies to all items)
|
| Table B2 is different here (it is for Powder)
| I have 15gm / 10
| 23gm/ 20
| 35gm/ 30
|
| While Table B1 is it is for Ribbons)
| 10cm/ 10
| 15cm/ 20
| 40cm/ 30
|
| So back to the Formula in Colum D THe middle Table must be selected
| based on the value in Column A
|
| I have to program this rather than build a complicated
| formula.Especially these tables are dynamic. Products can expand with
| the consequence of different B tables.
|
| In case you may be confused by my naming
| I call the table for column A A-Table and Column B B-Table etc
|
| Appreciate your direction in the matter
|

I used your technique in creating column A,B and C
I have "Validation" lists to construct the Item description (the 3
parts). So I do have a "NAME"List with the product in A

I beg you to imagine Col. A , B, C with Validation to actually help the
user pick his item. To do that I used the INDIRECT for B to select the
right spec based on A. Exactly as you descripted it but with validation.

My problem is to repeat the SAME TECHNIQUE as you proposed but with
different "Tables" in this case.

So, Col. A has Ribbon ( I have a NAME with the name Ribbon to direct me
to the spec for Ribbon.

NOW How can I used the SAME NAME but point me to TABLE instead of a
validation list; to VLOOKUP it.

In your Formula INDIRECT should use THE SAME name in A1 as a reference
to a table. BUT this A1 points to range name with the same name in order
to create it in the first place using data validation. I know I am
trying very hard to let you visualize the problem. The question is do
you have the patience!!!! I sincerly hope so



*** Sent via Developersdex http://www.developersdex.com ***

Given that Jim Rech took the time to read your first post and I did the
same with your 2nd, the last thing you should be doing is questioning
our patience.

Extend Jim's suggestion to names. Name your table for data on the
ribbon category as 'Ribbon' (w/o the quotes) using Insert | Name >
Define... Do the same with the table for 'powder.'

Now, the VLOOKUP(x,INDIRECT(A1),z) will become VLOOKUP(x,Ribbon,z) if
A1 contains Ribbon. XL will interpret the Ribbon reference in the
formula as a range name. Similarly, it will reference the Powder table
if A1 contains 'powder.'

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, [email protected]
says...
>
> I used your technique in creating column A,B and C
> I have "Validation" lists to construct the Item description (the 3
> parts). So I do have a "NAME"List with the product in A
>
> I beg you to imagine Col. A , B, C with Validation to actually help the
> user pick his item. To do that I used the INDIRECT for B to select the
> right spec based on A. Exactly as you descripted it but with validation.
>
> My problem is to repeat the SAME TECHNIQUE as you proposed but with
> different "Tables" in this case.
>
> So, Col. A has Ribbon ( I have a NAME with the name Ribbon to direct me
> to the spec for Ribbon.
>
> NOW How can I used the SAME NAME but point me to TABLE instead of a
> validation list; to VLOOKUP it.
>
> In your Formula INDIRECT should use THE SAME name in A1 as a reference
> to a table. BUT this A1 points to range name with the same name in order
> to create it in the first place using data validation. I know I am
> trying very hard to let you visualize the problem. The question is do
> you have the patience!!!! I sincerly hope so
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Thanks Tushar for your response.
If I go to Names I will find "Ribbon" is sitting there very happy doing
what I told it to do. Mainly waiting for the "Indirect" in the column B
validation to do the redirection. Let us say "Ribbon" NAME represent
Range (E1:E5) The specs for Ribbon.

Now you are asking me to Name another Range this time a Table say
Range (F1:G5) with the SAME name "Ribbon"

>From XL point of view both are ranges one column the other a table.

Range(E1:E5) will contain {20cm,30cm,40cm etc)
Range (F1:G5) will contain {20cm,10,30cm,20,40cm,40..etc)

Will XL be confused ? I will go to the drawing board and "I will be
back"....