Index Match With 3 Variables
 

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

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'!D1:D100,MATCH(('other_sheet'! A1:A100=A1)*('other_sheet'!B1:B100=A2)*('other_she et'!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

--
Regards
Frank Kabel
Frankfurt, Germany
"Scooterdog" schrieb im Newsbeitrag
...
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

I think Frank left out a 1:

=INDEX('other_sheet'!D1:D100,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 wrote:

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'!D1:D100,MATCH(('other_sheet'! A1:A100=A1)*('other_sheet'!B1:B100=A2)*('other_she et'!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

--
Regards
Frank Kabel
Frankfurt, Germany
"Scooterdog" schrieb im Newsbeitrag
...
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

--

Dave Peterson

Hi Dave
yes I did :-)
Thanks for the correction!

--
Regards
Frank Kabel
Frankfurt, Germany
"Dave Peterson" schrieb im Newsbeitrag
...
I think Frank left out a 1:

=INDEX('other_sheet'!D1:D100,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 wrote:

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'!D1:D100,MATCH(('other_sheet'! A1:A100=A1)*('other_sheet'!B1:B100=A2)*('other_she et'!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

--
Regards
Frank Kabel
Frankfurt, Germany
"Scooterdog" schrieb im Newsbeitrag
...
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

--

Dave Peterson

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

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(D1:D10,MATCH(1,(A1:A10=F1)*(B1:B10=G1)*(C1: C10=H1),0))

--
Regards
Frank Kabel
Frankfurt, Germany
"Scooterdog" schrieb im Newsbeitrag
...
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!!