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'!D1:D100,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

--
Regards
Frank Kabel
Frankfurt, Germany
"Scooterdog" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
> 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]

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_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
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> "Scooterdog" <[email protected]> schrieb im Newsbeitrag
> news:[email protected]...
> > 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

[Frank Kabel]

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

--
Regards
Frank Kabel
Frankfurt, Germany
"Dave Peterson" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
>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_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
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>> "Scooterdog" <[email protected]> schrieb im Newsbeitrag
>> news:[email protected]...
>> > 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

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

--
Regards
Frank Kabel
Frankfurt, Germany
"Scooterdog" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
> 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!!