CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulavlookuparray-formulas
Randy Adikara
Randy Adikara

Reputation: 387

vlookup an item within range

I was faced to a situation where I have to classify a list of numbers based on the size of the number.

For example, number between 2 and 8 is classified in class A. Number 16 to 25 is classified in class C. I can work on it with IF formula, but it doesnt feel good since it was like brute forcing IF within IF within IF and it is bad.

Is there any ARRAYFORMULA that can help me work on this? An arrayformula that doesnt need to be dragged down when data is added below. Maybe a formula that is related to VLOOKUP or any other formula will do.

This is the sample case:

https://docs.google.com/spreadsheets/d/1Dlrgp-aAlU2DknlG5u5XWJdXDpU5qj6EfofiCO3UTDQ/edit#gid=0

  1. Range A:A is the given data
  2. Range B:B is the desired outcome
  3. Range E:F is hte condition that has to be met

Upvotes: 0

Views: 693

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34400

You can do a lookup on the lower end of each range:

=ArrayFormula(if(A:A="","",vlookup(A:A,{split(E1:E9,"-"),F1:F9},3,true)))

enter image description here

assuming there are no values more than 1200.

Upvotes: 1

Related Questions