Approximataly match

[rahulshewale1]

hii sir,

PFA file.

i Need approxiamatly match with criteria multiple product and on/ off . City wise


Please help

Regard
Rahul shewale

 

[bosco_yip]

In J4, enter formula :

=LOOKUP(9^9,CHOOSE(ISNUMBER(SEARCH("on",$J$2))+1,LOOKUP(K2,$E$10:$F$16),LOOKUP(K2,$E$2:$F$6)))

Regards
Bosco

 

[rahulshewale1]

Hi SIr,

Thanks for Response ,

Actual my data is around 30000 row and there 15 products with on/off Criteria

i need slab and scheme base on vol with products with on/off.

i have attached sheet

lookup formula working fine but i need dynamic range with formula ( No offset Please )

Please Help



Regard
Rahul shewale

 

[bosco_yip]

In N3 copied right to O3 :

=LOOKUP($M3,CHOOSE(MOD(CODE($J3)&CODE(RIGHT($K3))&CODE($L3),6),$E$2:INDEX(E$2:E$30000,INDEX(MATCH(1,($C$2:$C$30000="")*($D$2:$D$30000=""),0)-1,0)),$E$28:INDEX(E$28:E$30000,INDEX(MATCH(1,($C$28:$C$30000="")*($D$28:$D$30000=""),0)-1,0)),"",$E$20:INDEX(E$20:E$30000,INDEX(MATCH(1,($C$20:$C$30000="")*($D$20:$D$30000=""),0)-1,0)),$E$10:INDEX(E$10:E$30000,INDEX(MATCH(1,($C$10:$C$30000="")*($D$10:$D$30000=""),0)-1,0))))

Regards
Bosco

 

[rahulshewale1]

Thanks you Sir,

But there are Total 15 product in original data. Excel will hanged with long formula ?

if i added one more product in sample sheet . Showing Wrong Result.

Please help

Regard
Rahul shewale

 

[r2c2]

Hmm, not sure what sorcery Bosco's formula is doing
But here is another one that seems to work with your data.

Slab: =INDEX(E$2:E$60, MATCH($M3,IF(($B$2:$B$60=$J3)*($C$2:$C$60=$K3)*($D$2:$D$60=$L3)=1,$E$2:$E$60,NA())))

Scheme: =INDEX(F$2:F$60, MATCH($M3,IF(($B$2:$B$60=$J3)*($C$2:$C$60=$K3)*($D$2:$D$60=$L3)=1,$E$2:$E$60,NA())))

Press CTRL+Shift+Enter to get the results.

Just change 60 to whatever end point your data is at.

This should be fine with large data as long as you are doing only one lookup, like the one in your file.

If you want to extract multiple results (in a table or something), I suggest combining and cleaning this data with Power Query first.

 

[rahulshewale1]

Thank so much @bosco_yip and @r2c2