IF 2 conditions match Then this...

[DevinJD2007]

Hello All,

I'm new to this forum and looking for a little help with a workbook that I'm putting together. What I'm attempting is if you look at the screenshot I've attached you should see a heading called "Body Area" in there I'm going to be making everything below it into a list. (I've written them out just to help with this example). The next two Columns you see are "Per Session" and "Package" what I'm going to do is make "Per Session" and "Package" into it's own list in it's own column.

Where I'm struggling is that what I would like to have is for example if someone selects "Eyebrows" from the Body Area list and then selects "Per Session" from the 2nd list I'd like the result to show the price ($75) in a different column. Same goes for if they select "Eyebrows" from the list and instead select "Package" I would like it to display ($450) in my other column and same on for the rest of the results you see written out.

Any help would be appreciated in getting this setup complete to where this would work. Please feel free to ask questions if you don't understand what it is that I'm asking!Capture.JPG

Thanks,

Devin

[ChemistB]

Lets say you have your first dropdown list in A1 and they choose Eyebrows

Then in A2, you have a dropdown list of either Per Session or Package

In A3
=INDEX($C$2:$D$29, MATCH(A1, $B$2:$B$29,0), MATCH(A2, $C$1:$D$1,0))
Does that work for you?

[DevinJD2007]

Hello ChemistB,

Thanks for the quick reply! So let me give this example say that Column B is where the "Body Area" parts will be in a drop-down list with those 28 things listed above in Cell B2. Then in Column C is where the drop-down list for "Per Session" or "Package" will be with the drop-down list in Cell C2. Then dependent on what they select I'd like for this to show up in Column D.

Also say that they make their first selection in Column B which is eyebrows from that list and then Column C which is "Per Session" for the $75 this result should show in Cell D2 then if they go down into the next list of selections Cell B3, C3, D3 it needs to do so on and so fourth down the workbook.

Hopefully this make sense using this exact example I used above.

Thanks,

Devin

[ChemistB]

Can you upload an example spreadsheet? (Go Advanced > Manage Attachments)

From your description, assuming your table is in sheet2
In D2

=INDEX(sheet2!$C$2:$D$29, MATCH(B2, sheet2!$B$2:$B$29,0), MATCH(C2, sheet2!$C$1:$D$1,0))

[DevinJD2007]

ChemistB,

I've attached the example workbook for you to look at. So as stated what I would like to have is if you select an option from the Column "Body Area" and a selection from "Per Session/Package" it would display the prices that are currently listed in columns E and F. Columns E and F WILL NOT be on the workbook itself though. I have just layed them out so you can see what Eyebrows would be "Per Session" ($75) "Package" ($450) and so on down.

Thanks,

Devin

[ChemistB]

Okay, if the prices won't be in Excel, how will Excel know what they are?

I created a second sheet which I called Table for your table of prices.
I also created a named range Table!A2:A29 = Body_Area
(Look at Name Manager on the Formulas Tab, assuming you're on Excel 2007 or higher)

Then in your data validation for col B, I replaced your list with = Body_Area

In Sheet1 D2 copied down

=IF(COUNTA(B2:C2)=2,INDEX(Table!$B$2:$C$29, MATCH(B2, Body_Area,0), MATCH(C2, Table!$B$1:$C$1,0)),"")

The "IF(CountA" part makes sure that both B and C are filled in or it just puts a blank.

The formula

INDEX(Table!$B$2:$C$29, MATCH(B2, Body_Area,0), MATCH(C2, Table!$B$1:$C$1,0))

looks on the Table sheet and matches up what you put in B and C to come up with a cost.

You can HIDE the Table Sheet if you want to.

[DevinJD2007]

ChemistB,

You're simply AWESOME! Thanks so much for all your time and help with this!

-Devin