Multiple conditions for IF Function

[MTC2016]

Hi Guys & Girls,


Got one that is causing me a few headaches... I have cell which needs to generate a value dependant on two types of style and two lots of categories. What I am looking for is a formula to recognise the following..


Cell H23 has two options - W or D which is a data validation drop down list.

Once W or D is selected, Cell A5 then has an INDIRECT data validation of categories to choose from which are based on the initial selection of W or D, which again is a drop down list to choose from.

Once the category in Cell A5 has been selected, then this will generate a value in Cell L4 which has an IF formula in it which states the correct numerical value based on the selected W or D and the relevant category selected in A5.

The problem I am having is I cant work out how to make the formula in Cell L4 recognise both the selections previously made in H23 & A5.

Can anyone point me in the right direction??

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[MTC2016]

Thanks Glenn,

I have created a workbook with the required data (the other main workbook has too many confidential bits to remove) You will see the initial formulas I have inserted into cells representing V1 - V5

I have populated a table below with the required results for each category for W & D

I just need the V1 - V5 boxes to automatically update with the correct values from the value data table based upon the type selection and then the category for that type which has been selected.

Many thanks

[MTC2016]

Just noticed their is a copy/paste error with the formulas in V1 - V5 but I am sure you will get my drift in terms of the requirements. My current basic formula doesn't provide me with the correct data anyway

[JohnTopley]

In G5

=INDEX($D$15:$H$18,MATCH($D$8,$C$15:$C$18,0),MATCH($F$5,$D$14:$H$14,0))

Copy to there cells and change Highlighted reference to appropriate "Vn" cell reference

You could then add IF condition to test for D or W

=IF (D5="D",INDEX($D$15:$H$18,MATCH($D$8,$C$15:$C$18,0),MATCH($F$5,$D$14:$H$14,0)), INDEX for W table)

[Glenn Kennedy]

Or this (after removing some trailing spaces after V1 in your data table)

=IFERROR(IF($D$5="D",INDEX($D$15:$H$18,MATCH($D$8,$C$15:$C$18,0),MATCH(F5,$D$14:$H$14,0)),IF($D$5="W",INDEX($D$21:$H$24,MATCH($D$8,$C$21:$C$24,0),MATCH(F5,$D$20:$H$20,0)),"")),"")

[MTC2016]

Thanks Gents! Problem Officially solved!

This is by far the greatest Excel forum on the internet!

[Xandler]

Please mark the thread as solved through the thread tools drop down if your initial question has been answered. Thank you.

[shukla.ankur281190]

Xandler, Do you really happy ??? I can give you more dynamic formula which will make you much happy ... Imagine you sample type more than 2 means 8 or 9 or much more then ????

You Can try

First make a helper column

B14

Please Login or Register  to view this content.

Drag down..

G6

Please Login or Register  to view this content.

Paste the same formula in other cells.

Check the attached file.