I am trying to work out a way to populate values in an excel table dependent on a range, for example, I have two tables:

I want to populate the Group number based on a look-up table:

So for any ID number that is between the Min and the Max ID in Table2, to get the Group number for that range, so in the Example tables above:

• John would get Group number 1
• Peter and Alex would get 2
• Dani would get 3

Table2 would be updated regularly and may grow in size as well.

I don't even know how to search for this properly, so apologies if this has already been answered elsewhere.. Any advise would be highly appreciated.

CodePudding user response：

Use INDEX()/MATCH() combination with match mode less than option. Try-

=INDEX($I$3:$I$5,MATCH(B3,$G$3:$G$5,1))

If you are on Microsoft 365 then can use XLOOKUP()

=XLOOKUP(B3,$G$3:$G$5,$I$3:$I$5,"",-1)

CodePudding user response：

Use Vlookup in the 4th parameter use True or 1 instead of False or 0.