Using Index/Match with Two Values in Another Sheet

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!