
0
In Sheet 1, I like the prices for services for different vendors:
Vendor 1 | Category 1 Service | $10
Vendor 1 | Category 2 Service | $20
Vendor 1 | Category 3 Service | $50
Vendor 2 | Category 1 Service | $70
Vendor 2 | Category 2 Service | $90
Vendor 3 | Category 1 Service | $30
Vendor 3 | Category 2 Service | $60
In Sheet 2, I want to calculate the cost for a customer based on (1) the vendor the select and (2) the service they select. In the cell, I want it to show $20, if in the two other cells they selected Vendor 1 and Category 2.
I know how to use INDEX/MATCH to find the price for one variable (e.g., vendor or category) in Sheet 1 by matching what they selected from one of the drop downs in Sheet 2:
=INDEX({Category-Price}, MATCH([Category Selected]@row, {Category Description}, 0), 2)
{Category-Price} is range in Sheet 1 that includes the categories and prices. [Category Selected]@row is the category they selected in Sheet 2 from the drop-down box. {Category Description} is the range in Sheet 1 where it matches that category selection.
Now that I've added more than one vendor, I need to not only match the category but also the vendor, to determine the price. Is there a way to find the price in Sheet 1 by using the Vendor # and Category #?
Comments
Paul Newcome
June 5, 2019 10:02 am
aschneiderhein…
June 5, 2019 10:31 am
In reply to Try using something like… by Paul Newcome
Paul Newcome
June 5, 2019 10:36 am