If formula to match 2 conditions and vlookup to match a 3rd condition

In the xlsx attached, I want cell b4 to return a calculated value based on the what is selected in cells b1 and b2, and what is manually input in cell b3. The reference sheet for how to calculate the value to be returned in b4 in provided on the "References" worksheet. For example, if on "Sheet1", the user selects "Seller 1" in b1, "Auction" in b2 and enters a list price of 200,000, i would like b4 to return a value by going to the "References" worksheet, looking for "Seller 1" and then "Auction", and then multiply cell b3 on "Sheet1" by 77% (i.e. the % indicated where "Seller 1" intersects with "Auction"

Thank you!!

I inserted a new column B in your References sheet and put the lower limit of each range in there, i.e.:

B3: 0
B4: 25000
B5: 75000
B6: 150000
B7: 300000

You can copy these to the lower cells for completeness, and you can hide this column if you want the sheet to look as before.

Then in cell B4 on Sheet1 you can use this formula:

=INDEX(References!$D$3:$H$17,MATCH($B$2,References!$A$3:$A$17,0)-1+MATCH($B$3,References!$B$3:$B$7),MATCH($B$1,sellers,0))

Make your choices in cell B1 to B3, as appropriate.

Hope this helps.

Pete

Hi Pete, I am looking at this now. Any chance you could attach the xlsx you worked off of?

File duly attached.

Pete

Thank you so much Pete! Just one thing, if I enter a value of 25,000 for example (right on the threshold of a price band), the % it returns is for the next higher band. E.g. if "Seller 3", "Auction" are selected, with list price = 25000, the output is .58 when it should be .55.. any ideas how to overcome this?

Well, the way you have it listed in column C implies that the band is from 0 to 24,999 then 25,000 to 74,999 and so on. If you want the bands to be 0 to 25,000, then 25,001 to 75,000 you just need to add 1 onto the values in B4 to B7, i.e. 25001, 75001 etc., as these are the lower limits of the ranges.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Pete, thank you SO much! This has been most helpful and works well. One thing I wish to understand (if you wouldn't mind explaining) is why before the second MATCH function, you put -1+MATCH. i.e., why does the row number have to be -1+ and then MATCH as opposed to just MATCH? Thanks again, your time and effort is really appreciated

There are two MATCH functions to determine the row where you want to look up the data from. The first looks for the Type, and because of the merged cells in column A it will return 1, 6 or 11 (these are relative row numbers). The second MATCH will return from 1 to 5, depending on the value of Price, so if those two values are added together they will result in row numbers in the range 2 to 16 - the minus 1 ensures that they are adjusted to within the range defined by the table in the INDEX part of the formula.

Another way to think about it is the first MATCH determines the block number, or offset, and the second MATCH determines the row within the block, which is why my -1 follows the first MATCH.

Hope this helps.

Pete

Pete, you're the man! Thank you very much & wishing you all the best!