Index Match With 3 Variables

[Scooterdog]

Could someone please give me a simple "example" and formula
of a index match using 3 variables, if there is such a thing.
2 of the variables are in text (a1 & a3) and 1 variable (a2)
is numeric.
Thank you in advance for your time

 

[Frank Kabel]

Hi
not really sure what you're trying to do. You may give more details what
you're looking for. But as a guess try the following array formula (entered
with cTRL+sHIFT+ENTER):
=INDEX('other_sheet'!D1100,MATCH(('other_sheet'!A1:A100=A1)*('other_sheet'!B1:B100=A2)*('other_sheet'!C1:C100=A3),0))

this searches for a match of A1:A3 in the columns A to C on a different
sheet and returns the corresponding value from column D

 

[Dave Peterson]

I think Frank left out a 1:

=INDEX('other_sheet'!D1100,MATCH(1,('other_sheet'!A1:A100=A1)
*('other_sheet'!B1:B100=A2)*('other_sheet'!C1:C100=A3),0))

(still ctrl-shift-entered and all one cell)

 

[Frank Kabel]

Hi Dave
yes I did
Thanks for the correction!

 

[Scooterdog]

Mr. Kabel, I "think" this will help you to understand
what I am trying to do!
If you would, go to the address:
www.contextures.com/xlFunctions03.html
Using the table shown, I would like to add in Column E1
the word Style. E2 would have letter s, E3 would have
letter f, and E4 would have the letter w.
Now, in C6 I would have the word Style and, in D6 would
be the word Price.
To get the correct price would require in row 7 the following:
Med, Pants, f to get correct Price.

I "think" this would require 3 variables, if I understand the
index match formula.
Does this help any or make sense?
I thank you for all your time and patience with me.
Have a good New Year!!

 

[Frank Kabel]

Hi
this layout would make no sense
Currently this is a 3-dimensional matrix (so looking for the header and the
row). You seem to want to have a 3-dimensional matrix (which is directly not
supported in Excel).

You could create a table which looks like the following:
A B C D
1 topic size style value
2 t1 10 s 1
3 t1 10 e 2
4 t1 10 g 3
.......


Now assume you have in F1 the topic, G1 the size and H1 the style to look
for then try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(D110,MATCH(1,(A1:A10=F1)*(B1:B10=G1)*(C1:C10=H1),0))