Using Index/Match with Two Values in Another Sheet

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



  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try using something like this instead...


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

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes


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

Sign In or Register to comment.