Find data in table

[madkiwi]

I am currently trying to work out how to return a row label based on the position of a value found in a table.

My table has 4 rows and 7 columns of data (plus a row and column of labels). I want Excel to find my formula's value in the table and return the label for that row.

This is to do with working out the weight of a First Class Mail Package based on the amount charged. Obviously I could do a nested IF x=y formula to match it up, but that will be 28 nested IF functions.
I would prefer not to. The cell to be returned is the weight of the package, the USPS has 7 different shipping rate zones...

Thank you!

 

[Eric W]

Try this array formula:

	A	B	C	D	E	F	G	H	I
1		Zone1	Zone2	Zone3	Zone4	Zone5	Zone6	Zone7
2	Row1	1	2	3	4	5	6	7
3	Row2	8	9	10	11	12	13	14
4	Row3	15	16	17	18	19	20	21
5	Row4	22	23	24	25	26	27	28
6
7	19	Row3

<tbody>
</tbody>

Sheet18​

Array Formulas Cell Formula B7 {=INDEX($A$2:$A$5,SMALL(IF($B$2:$H$5=A7,ROW($B$2:$H$5)-ROW($B$2)+1),1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If there's any kind of pattern to the values, it may be possible to create a non-array formula.

 

[madkiwi]

Thank you, this worked (as part of a much longer formula with the table on another page).

At first I thought something was going wrong, used the Ctrl-Shift-Enter to create the formula, then needed to edit something (hadn't fixed the table references with $) and the formula was broken. Took me about 15 minutes to realize the curly braces disappear when you start to edit it!

All working now!

Mark

 

[Eric W]

Glad you got it working!