Adding third lookup value to index/match

[J.W. Aldridge]

I understand how Index/Match works with double lookups... But I cant
quite get the third lookup down.

Looking to add a third lookup value and range to this formula (range
z)

=INDEX(table, MATCH(x,range x,0), MATCH(y,range y,1))

Data is laid out like so... (Vertical column range is x)

A B C D E F G H
1 range yyyyyyyyyyyyyy ->
2 range zzzzzzzzzzzzzz ->
x
x
x
x
x
x


any suggestions...?

 

[T. Valko]

How does the 3rd range relate?

Need more specific details.

 

[J.W. Aldridge]

Thanx...!

The first range will be x (vertical). (contains 4 letter codes)
The first range will be y (horizontal) (contains dates).
The first range will be z (horizontal) (contains times)

 

[T. Valko]

Ok, so you probably want a specific time for a specific date.

What does the setup look like? Can you post a sceencap?

Is there a different date in every cell in range Y? What about the times in
range Z?

How are the times associated with a date?

This should be no problem but I can't suggest something without knowing how
the data is setup.

 

[J.W. Aldridge]

X Search value is = FRUIT (cell g20)
Y Search value is = 09:00 (cell g21)
Z Search value is = Oct-4 (cell g22)

Desired result = 16

Need to add criteria and range z.

=INDEX(table, MATCH(x,range x,0), MATCH(y,range y,1))


Table starts with cell A1.

B C
D E
A1 08:00 09:00 10:00
11:00
A2 Oct-4 Oct-4 Oct-3
Oct-3
APPLES 10 20 30 40
BANANAS 20 30 40 50
CHERRIES 60 70 80 90
DUNG 91 92 93
94
EGGPLANT 11 12 13 14
FRUIT 15 16 17
18
GRAPES 19 20 21
22

 

[Peo Sjoblom]

This works for me

=INDEX(A1:I20,MATCH("fruit",A1:A20,0),MATCH(1,(A1:I1=TIME(9,0,0))*(A2:I2=DATE(2007,10,4)),0))


change the cell references accordingly to fit your size

It needs to be entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom

 

[J.W. Aldridge]

Thanx.

Since I have cell references for the criteria I am searching for,
could something like this work?

=INDEX(A1:E13,MATCH(K2,A1:A13,0),MATCH(1,(A1:E1=K3)*(B2:E2=K4),1))

 

[Peo Sjoblom]

Yes if you put those values in K2 - K4 and enter the formula with ctrl +
shift & enter it should work
If it doesn't then there is no match meaning that somehow what you put in K3
and K4 might differ. You can easily check that by direct comparison like
=K3=C1 where K3 holds 9:00 and presumably C1 does too. If it returns FALSE
you will need to find if the values in A1:E1 are text, do the same for
K4 and C2


--


Regards,


Peo Sjoblom