Lookup based on ">" and "<"

[BMil8]

Hello,

I'm trying to find a formula that can do a price lookup in cell I5 based on multiple criteria. I would like it to find the company name from G5 in column B and then look at transactions in H5 and determine which range from C:D it falls within and then return the price from column E. Some type of LOOKUP formula? Any thoughts?

Thank you,

 

[Fluff]

How about

=TAKE(FILTER(E3:E100,(B3:B100=G5)*(C3:C1000<=H5)),-1)

 

[BMil8]

How about

=TAKE(FILTER(E3:E100,(B3:B100=G5)*(C3:C1000<=H5)),-1)

Returns #VALUE!

 

[Fluff]

That suggests that no matches were found.
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[BMil8]

I seem to be having trouble finding the Add-In. I'll keep trying. It would be the same sample data in the screenshot above (but I understand that's harder to work with).

 

[Fluff]

The problem maybe that your numbers are actually text, or you have leading/trailing spaces in some cells, there is no way to check that with an image.
Does this work any better

=SUMIFS(E:E,B:B,G5,C:C,"<="&H5,D:D,">="&H5)

 

[BMil8]

I was able to modify the formula you game me and return the expected result.

=TAKE(FILTER(E3:E100,(B3:B100=G5)*(C3:C100<=H5)*(D3:D100>=H5)),1,4)

Does that look OK to you? If so, thanks for the help!

Editing this post for the 3rd time. It also looks like your original formula works if I change the 1000 to 100.

 

[Fluff]

You don't need the final ,4 in the formula as it's only returning a single column, but other than that it's fine.