Find which row a value fits in

[Jimmyjazz]

For each cell in Column A, I want to find out which row in another table it belongs in.

The tricky part is that the other table has a low and a high value.

So I'm checking:
1 Is A1 between the numbers in the first Row. If so, return "Row 1", otherwise
2 Is A1 between the numbers in the second Row. If so, return "Row 2", otherwise
3 Is A1 Between the numbers in the third Row. If so, return "Row 3", etc


One page said something about using MEDIAN, which didn't work for me. Not sure if an array formula works (those hurt my brain). I'd like to not use a huge string of IFs, if possible.

Is there some easy function I'm missing here?

[protonLeah]

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[Tony Valko]

Maybe something like this...


Data Range

	A	B	C	D
1	19	-----	Low	High
2	Row 2		1	10
3			11	20
4			21	30
5			31	40

This formula entered in A2:

="Row "&MATCH(A1,C2:C5)

[Jimmyjazz]

example.xlsx

hopefully this attachment works

The full workbook is huge and complicated. This is just a simplified page. The yellow cells are the ones I'm trying to fill in.

[Tony Valko]

Ok, you want the formula to go into column F.

So which number are you looking for in the Low/High table? The Price or the Lv?

[Jimmyjazz]

Oh whooops forgot to say.

It's the column D that should be placed into one of the ranges.

[Tony Valko]

Try one of these:

=LOOKUP(D4,D$19:D$22,C$19:C$22)

=INDEX(C$19:C$22,MATCH(D4,D$19:D$22))

[Jimmyjazz]

It works!
I don't know how it works though. It's only comparing it to the low limits. How'd you do that??

[Jimmyjazz]

There's actually a slight hitch.

The actual tiers have gaps in them. Like Tier 1 will be between 0 - 15 and tier 2 will be between 18 - 32.

So when I use either of those codes, if the cell i'm referencing = 16 or 17, it calls it Tier 1. I'd rather it just be an error, and I can use IFERROR to make it 0.

If there's no solution, I could make null tiers from 16-17 for each pocket between real tiers.

[Ghozi Alkatiri]

at F4
=INDEX($C$19:$C$22,MATCH(D4,$D$19:$D$22))
copy down

[sktneer]

Sort the high limit from high to low and then use the below formula......

Please Login or Register  to view this content.

Please see the attachment.

[Jimmyjazz]

Ghozi, that's the same as the one Tony posted.

sktneer, the same thing happens for the gaps, they get lumped into the nearest tier.

So weird that there's not a function to do this. I would have imagined a "if between" nested in a vlookup somehow.

[sktneer]

Try this. I hope it works as per your requirement. See the attachment.

Please Login or Register  to view this content.

[Tony Valko]

Try this...


Data Range

	C	D	E	F
3	Name	Lv	Price	Tier
4	Glove	1	20	Tier 1
5	Glove	17	25	Tier 2
6	Glove	12	30	NA
7	Hat	3	20	Tier 1
8	Hat	11	25	NA
9	Hat	37	30	Tier 4
10	Hat	27	35	Tier 3
11
12	Tiers	Low Limit	High Limit
13	Tier 1	0	10
14	Tier 2	15	20
15	Tier 3	22	30
16	Tier 4	37	58

This array formula** entered in F4 and copied down:

=IFERROR(INDEX(C$13:C$16,MATCH(1,IF(D4>=D$13:D$16,IF(D4<=E$13:E$16,1)),0)),"NA")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Jimmyjazz]

Brilliant. I don't know how you guys do it, my brain just can't grasp it all in order to put the pieces together.

Thanks so much!

[Tony Valko]

You're welcome. Thanks for the feedback!