PDA

View Full Version : Solved: Lookup Help



xls
06-19-2010, 07:43 AM
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
06-19-2010, 09:16 AM
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
06-19-2010, 09:48 AM
Gr8, is there any other ay to find out without using array & sumproduct.:clap:


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
06-19-2010, 09:58 AM
didn't use sumproduct..

xls
06-21-2010, 10:34 AM
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.
:banghead:

Bob Phillips
06-21-2010, 10:40 AM
Are you expecting a list, or will there only be one? Why not an array formula?

xls
06-21-2010, 09:09 PM
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
06-22-2010, 12:11 AM
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
06-23-2010, 09:04 AM
Dude i didnt mean that.

Pl post solution i possible.

p45cal
06-23-2010, 10:27 AM
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
06-25-2010, 09:12 PM
Thanks dear it is working.

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