Using Index/Match with Two Values in Another Sheet

Art Schneiderheinze

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 #? 

Paul Newcome

Try using something like this instead...

 

=JOIN(COLLECT({Category-Price}, {Category Description}, [Category Selection]@row, {Vendor}, [Vendor Selection]@row))

Art Schneiderheinze

Oh yeah! Completely forgot about using the JOIN/COLLECT! Thanks!

Paul Newcome

Happy to help! 

 

I still forget about it sometimes because my initial thought is not that I am trying to JOIN cells together. Hahaha

Shirley Pruitt

If possible, please take this one step further, having the results based on a checked box on the other sheet. Currently, based on the example above, this is working for me:

However, I need it to return the Key Number that has the "Assign" box checked on the other sheet:

I would very much appreciate help with this. Thank you.