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" wrote in message
oups.com...
| 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
|