XLooup Help in Excel

Question

Trying to use Xlookup and cant seem to get it working. Here is what I need to achieve.

Primary Sheet. I have a large number of rows that contain a value in column G - say it has various numbers between 3000 and 5000 on diff rows.

Secondary Sheet. I have a table that contains ranges of from / to values - say

Col A Col B

3000 3117

3856 4200

4310 4315

4667 4995

I want to use Xlookup to look for the value in column G and see if its in any of the ranges in my table.

Example - Column G has a value of 4312 - this is found in the range 4310 - 4315 so return the value of "yes". Next Row, column G has value of 3116 - its in the first range so return "yes". Next row, column G has a value of 4444. Its not in any of the ranges so return "no".

Thanks

Answer 1

Hi,

If you are OK with 1's and 0's, then enter this formula in cell E2

=BYROW(D2:D4,LAMBDA(s,SUM((s>=A2:A5)*(s<=B2:B5))))

Hope this helps.

Answer 2

Trying to use Xlookup...

I have a large number of rows that contain a value in column G

================

Yes, you would use xlookup to reduce the number of iterations needed....