Printable View
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
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))
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.
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
Thanks for the feedback :cheers: