View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Tiffany Tiffany is offline
external usenet poster
 
Posts: 53
Default Lookup function with mulitple conditions

Hi Max,

Thank you very much. Your formula works perfectly for me. Cheers!

"Max" wrote:

One way ..

Assuming the table posted is within A1:G71,
col A = Index, col C = Main Cat, col D = Sub Cat, col E = Brand

Assuming I1:K1 will house the inputs for Main Cat, Sub Cat & Brand,

Put in L1's formula bar, then array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=IF(COUNTA(I1:K1)<3,"",INDEX($A$2:$A$71,MATCH(1,($ C$2:$C$71=I1)*($D$2:$D$71=J1)*($E$2:$E$71=K1),0)))

As-is, L1 can be copied down to return correspondingly
for other sets of inputs in I2:K2, I3, K3 etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiffany" wrote:
I need some help in the lookup function. Below is the excerpt of my
database. How can I pick up the right row given 3 conditions to satisfy, ie
Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI,
Brand = PIONEER, what formula should I put in order to pick up Index = 24.
Kindly advise.

Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06
1 1 AHS ALL ALL 112,515 123,050
2 2 AHS ALL SONY 14,509 15,074
3 3 AHS ALL LG 13,056 17,837

...
69 0 CTV FPD LG 6,379 11,865
70 0 CTV FPD PHILIPS 9,442 14,957