2 level Vlookup (1 is exact match 1 is approximate match)

[Ganesh Ravichandran]

I have a table as attached.

Column B has a range of categories (Duplicates will be present). Column C and D contain the minimum and maximum quantity. Column E contains the Number of days required.

Now input would be the category and the Quantity. Output should be the number of days.

Any help would be great

 

[Deepak]

Check this..

=SUMPRODUCT((H4>=$C$3:$C$180)*(H4<=$D$3:$D$180)*($B$3:$B$180=H3))

 

[Ganesh Ravichandran]

Thanks for your quick help Deepak.

I guess I should have made the example with more details. For the combination of "Category" column (B) and specific quantity which lies between the range specified in Min (C) and (D), the output should be returned from "Days" Column (E).

Values specified in Cell H3 are specific and would be only from column B. However values in H4 would be any random number; however within the predetermined range of column C and D.

Example: 1
H3 and H4 are "Mobiles" and "1809", H7 should be calculated as 2

Example: 2
H3 and H4 are "IT" and "9101", H7 should be calculated as 5.

 

[Khalid NGO]

Hi Ganesh Ravichandran, welcome to the forum

Check this array formula also:

=INDEX(E3:E180,MATCH(H3,IF(H4>=C3:C180,IF(H4<=D3:D180,B3:B180)),0))

to be entered with Ctrl+Shift+Enter (not just enter)

Regards,

 

[Ganesh Ravichandran]

Boom! That worked @Khalid NGO . Thanks for the help and the warm welcome

 

[Deepak]

A little correction. {thanks @Khalid NGO }

=SUMPRODUCT((H4>=$C$3:$C$180)*(H4<=$D$3:$D$180)*($B$3:$B$180=H3)*$E$3:$E$180)

 

[Deepak]

oh.
I missed it.

=SUMIFS($E$3:$E$180,$C$3:$C$180,"<="&H4,$D$3:$D$180,">="&H4,$B$3:$B$180,H3)

 

[Ganesh Ravichandran]

Thanks @Deepak. This works as well. So I have a formula with using arrays and with a SUMIFS. Thanks!

 

[Khalid NGO]

Boom! That worked @Khalid NGO . Thanks for the help and the warm welcome

welcome dear.
Keep visiting.

oh.
I missed it.

=SUMIFS($E$3:$E$180,$C$3:$C$180,"<="&H4,$D$3:$D$180,">="&H4,$B$3:$B$180,H3)

Really cool solution, Thank you Sir.