# Find data in table

##### New Member
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!

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Try this array formula:

ABCDEFGHI
1Zone1Zone2Zone3Zone4Zone5Zone6Zone7
2Row11234567
3Row2891011121314
4Row315161718192021
5Row422232425262728
6
719Row3

<tbody>
</tbody>
Sheet18

Array Formulas
CellFormula
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.

Last edited:
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

Replies
1
Views
175
Replies
4
Views
154
Replies
3
Views
107
Replies
0
Views
71
Replies
8
Views
201

1,203,605
Messages
6,056,270
Members
444,853
Latest member
sam69

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back