Solved: Lookup Help

[xls]

Hi following is example of table in which data is available:

I want data from table which specifies condition1 = a, condition 2 = 3 & Month=2. How to find out using formula.


Cond. 1.....Cond. 2.......Month1..........Month2..........Month3
......a.............. 1..............Data1............ Data12.............Data13
......a.............. 2..............Data2............ Data12.............Data13
......a.............. 3..............Data3............ Data12.............Data13
......a.............. 4..............Data4............ Data12.............Data13
......a.............. 5..............Data5............ Data12.............Data13
......b.............. 1..............Data1............ Data12.............Data13
......b.............. 2..............Data2............ Data12.............Data13
......b.............. 3..............Data3............ Data12.............Data13
......b.............. 4..............Data4............ Data12.............Data13
......b.............. 5..............Data5............ Data12.............Data13

Pl help

[p45cal]

In B22 Array-Enter (Ctrl + Shft + Enter, not just Enter) the following formula:
=INDEX($A$3:$E$14,MATCH(1,--($A$3:$A$14=$B$18)*($B$3:$B$14=$B$19),0),MATCH($B$20,$A$3:$E$3,0))
('orrible)
It'll only find the first one.

[xls]

Gr8, is there any other ay to find out without using array & sumproduct.

In B22 Array-Enter (Ctrl + Shft + Enter, not just Enter) the following formula:
=INDEX($A$3:$E$14,MATCH(1,--($A$3:$A$14=$B$18)*($B$3:$B$14=$B$19),0),MATCH($B$20,$A$3:$E$3,0))
('orrible)
It'll only find the first one.

[p45cal]

didn't use sumproduct..

[xls]

didn't use sumproduct..

Yes, i know. And thanks for posting answer.

Anybody pl suggest me another way is possible. Or it reuiqres creating UDF.

[Bob Phillips]

Are you expecting a list, or will there only be one? Why not an array formula?

[xls]

Thanks for reply. Yes some place there is a list & at some place only one.

I dont want array since is required using (ctr + shft + entr) after entering data. Many user dont know it & i dont want to use another control for prevating the same.

[Bob Phillips]

I would not expect users to enter it, infact I would never expect users to enter formulae in my production worksheets. You should design it so that they only enter data, not code.

[xls]

Dude i didnt mean that.

Pl post solution i possible.

[p45cal]

is there any other ay to find out without using array & sumproduct.

Dude, perhaps you meant by the above:
"is there any other way to find out without using array-entered formulae, but instead with SumProduct"

??

If so (same sheet as before):

=INDEX($C$3:$E$14,SUMPRODUCT(($A$3:$A$14=B18)*($B$3:$B$14=B19),ROW($A$3:$A$14)-ROW($A$3)+1),MATCH(B20,$C$3:$E$3,0))

[xls]

Thanks dear it is working.

can you explain sumproduct part. I did not understood second part where you are subtracting??