Index and match function multiple criteria

[mfortier3]

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.

[daddylonglegs]

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))

[Max, Singapore]

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

[hawgdaddy]

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?

[Max, Singapore]

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