Lookup to search between two values - not working

New Contributor

Hi,

 

I want to search between two values, and return a result. I have found this formula on multiple sites, but it isn't working for me.

 

=Lookup(2;1/((H9>=groeps!A3:A4)*(H9<=groeps!B3:B4));groeps!C3:C4)

H9 is my lookupvalue

A3:A4 minimum,

B3:B4 maximum

C3:C4 result

 

In my Excel version I need to use semicolon instead of comma, do I need something else for the * too?

2 Replies

@Celia9 

It should work. Perhaps you need to confirm the formula with Ctrl+Shift+Enter.

Groeps sheet:

S1266.png

Sheet with formula: values between 11 and 30 return a valid result, others return #N/A.

S1267.png

(I use comma as list separator)

I found it.. I had a table that was named 'lookup', and Excel automatically converts LOOKUP formula to Lookup (the table).