# Index and match function multiple criteria

1. ## Index and match function multiple criteria

excel.png

Based on the image above, is it possible to use the index and match functions (or any other function) to lookup and return the price based on the color, size and type of fabric? I'm hoping to do this without changing the layout of the data. The attached image depicts a very simplified example of how the data is actually laid out. I realize it would be possible to use the index and match functions if I devoted one column to fabric and one column to the price, but since I'm dealing with so much data it would take a long time to rearrange and re-enter everything.

Thanks.

2. ## Re: Index and match function multiple criteria

If you put a specific colour in A10, specific size in B10 and then specific fabric in C10 try this formula in D10 for the price

=INDEX(C3:D8,MATCH(1,INDEX((A3:A8=A10)*(B3:B8=B10),0),0),MATCH(C10,C2:D2,0))

3. ## Re: Index and match function multiple criteria

Yes, one way is via index/match, with the match done on multiple conditions
Indicatively, assuming you have the 3 variables running in A2:C2 down
you could have this in D2, normal ENTER to confirm:
=index(Price,match(1,index((Color=a2)*(Size=b2)*(Fabric=c2),),0))
-------------------
Checks out fine? Wave it, click the little star at the bottom left of my responses

4. ## Re: Index and match function multiple criteria

I'm looking at the first example above, and have a question:

=INDEX(C3:D8,MATCH(1 etc, etc

What is the purpose of the 1?

5. ## Re: Index and match function multiple criteria

The 1 is simply the value to match, within the match array
The match array, ie this part in daddylongleg's expression: INDEX((A3:A8=A10)*(B3:B8=B10),0),0)
will resolve to an array of 1's and 0's, eg: {0,0,1,0 ... }
depending on where the multiple conditions are simultaneously satisfied (1's) or not (0's),
hence MATCH(1,<match array>,0) will return the position of the 1 which matches within the match array
---------
Success in understanding? Wave it, whack the little star at the bottom left of my responses

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1