Help with combination of if and vlookup formula

**Fbs1960** · 11-13-2011, 01:00 AM

In cell a1 have a range of 12 different selections (product names) which are selected from a drop down box.
In cell b1 have a range of 14 other selections (sub products of cell a1) which are also selected from a drop down box.
In cells c1,d1,e1 and f1, I have dimensions of product. In cell g1 I want the weight of the product.
What I am trying to achieve is that if a1 =x and b1 = y, then cell g1 will look at a formula that is specific to product code of x and y.
If a1 = xx and b1=zz then cell g2 will look at a different weight formula.
Apologies if I have not explained clearly enough
regards FBS

**jeffreybrown** · 11-13-2011, 01:06 AM

Hello & Welcome to the Board,

Do you have a sample workbook to display what you have and what you desire?

**Fbs1960** · 11-13-2011, 04:05 AM

Originally Posted by jeffreybrown

Hello & Welcome to the Board,

Do you have a sample workbook to display what you have and what you desire?

Attached is spreadsheet I am working on.

Each row has a different material and Grade which require a different formula in the case of material to calculate approximate weight and each grade has a different weight factor (density) which is required to calculate the weight. (weight formula guideline is listed beside spreadsheet with grade weight factors listed in sheet (data cells O2 to P23)

**Fbs1960** · 11-19-2011, 07:24 PM

Jeffreybrown
Update spreadsheet attached as per PM sent yesterday.

Hope the overview of problem was correctly explained in PM
Thanks

**Fbs1960** · 11-19-2011, 08:56 PM

I have been playing around with the spreadsheet further.

In cell S10 I have added a Vlookup formula.

I now have an if formula in cell N10 which is matching cell C10 with S10 then looking for a corresponding match in U9 to AC9 and then when it matches taking the corresponding weight value in row 10.

The issue I have now is that I can only do a maximum of 7 nested IF functions and I may need to go up to 15 to 20. At this stage there are 9.

I hope the updated assists.

Many Thanks

**jeffreybrown** · 11-19-2011, 09:08 PM

Try this in N10...

=INDEX($T10:$AC10,MATCH(S10,$T$9:$AC$9,0))

**Fbs1960** · 11-19-2011, 09:24 PM

Excellent. That works perfectly. Many thanks.

To get rid of #NA in cells in column N that do not have any data in column C, can I apply an isblank formula before the index function.

**jeffreybrown** · 11-19-2011, 09:28 PM

If it is 2003 you are using...

=IF(ISNA(INDEX($T10:$AC10,MATCH(S10,$T$9:$AC$9,0))),"",INDEX($T10:$AC10,MATCH(S10,$T$9:$AC$9,0)))

...if you are 2007 or above...

=IFERROR(INDEX($T10:$AC10,MATCH(S10,$T$9:$AC$9,0)),"")

or another option any version...

=IF(S10<>"",INDEX($T10:$AC10,MATCH(S10,$T$9:$AC$9,0)),"")

**Fbs1960** · 11-19-2011, 10:01 PM

All worked fine. Thanks for your help. Been working on this for a few days. wish I had found this forum much earlier

**jeffreybrown** · 11-19-2011, 10:04 PM

You are very welcome...glad I could help in some way

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save