Lookup on Multiple Criteria

[ramakrishnan]

Dear Experts,

I have a work book in sheet-1 i have made the grids based on certain multiple parameters. In sheet-2 the data sheet the values to be mapped from column F2 TO K2 based on the parameters set on the sheet-1. Where ever there is no values available it should be displayed as N/A. Can some one help me out to get a code to do this task automatically using a macro.

Attached the sheet for your kind reference.


Thanks in Advance

R.Ramakrishnan

[Excel Fox]

Try this formula in Grid!F2 and copy the formula across the adjacent cells

=SUMPRODUCT((Grid!$A$2:$A$1000=Data!$B2)*(Grid!$D$ 2:$D$1000<=Data!$D2)*(Grid!$E$2:$E$1000>=Data!$D2) *(Grid!$F$2:$F$1000<=Data!$E2)*(Grid!$G$2:$G$1000> =Data!$E2)*(Grid!H$2:H$1000))

[Admin]

Hi RK,

Anoher one..

In F2 and copied down and across,

=LOOKUP(2,1/((Grid!$A$2:$A$25=$B2)*($C2>=Grid!$B$2:$B$25)*($C2 <=Grid!$C$2:$C$25)*($D2>=Grid!$D$2:$D$25)*($D2<=Gr id!$E$2:$E$25)*($E2>=Grid!$F$2:$F$25)*($E2<=Grid!$ G$2:$G$25)),Grid!H$2:H$25)


Using INDEX/MATCH

=INDEX(Grid!H$2:H$25,MATCH(1,IF((Grid!$A$2:$A$25=$ B2)*($C2>=Grid!$B$2:$B$25)*($C2<=Grid!$C$2:$C$25)* ($D2>=Grid!$D$2:$D$25)*($D2<=Grid!$E$2:$E$25)*($E2 >=Grid!$F$2:$F$25)*($E2<=Grid!$G$2:$G$25),1),0))

It's an array formula. Confirm with CTRL + SHIFT + ENTER, not just ENTER.

[ramakrishnan]

Sir, It is really excellent !!!!!!!!!!! Thanks a million !!!! The formula is very much useful which will same much of my time,

Thanks very much.........

This forum is really good as i have become a freequent visitor. The solutions are received quickly . Thanks for the quick response.

Regards
R.Ramarishnan

[Admin]

Thanks for the feedback