excelexcel-formulaconditional-statementscelllookup

If a number is in a range tell me the name of the range. (the value in the next column)

I have a table if ranges of number and the name of each range like that:

Color   Wavelength (nm)
Red 626 - 740
Orange  591 - 625
Yellow  566 - 590
Green   521 - 565
Cyan    501 - 520
Blue    436 - 500
Violet  380 - 435
Color Wavelength (nm)
Red 626 740
Orange 591 625
Yellow 566 590
Green 521 565
Cyan 501 520
Blue 436 500
Violet 380 435

Then in a column I have a long list of numbers and i have to tell the color of the number.

The list is not just that, all de electromagnetic spectrum is in this list of ranges.

How can I make this work without a gigantic IF inside IF chain?

For shorter lists I usually make a chain of IFs one for each interval.

But this will be too big.

Solution

  • Using Xlookup:

    =IF(E2<B$8,"",XLOOKUP(E2,C$2:C$8,A$2:A$8,"",1,-1))

    Or as a spill formula:

    =LET(inputs,DROP(TOCOL(E:E,1),1),
IF(inputs<B8,"",XLOOKUP(inputs,C2:C8,A2:A8,"",1,-1)))

    enter image description here