# Using OFFSET for Rows AND Columns? (SOLVED)

#### Rylandir

##### New Member
Hi all,

I have a table set as # of users down 1 column (users 1-5), and # of PCs across 3 rows (PCs 0, 1, 2). A support price is set at the intersection of the # of users and the # of PCs.

For example, the table may look like this:

0 1 2
1 \$100 \$200 \$300
2 \$100 \$300 \$400
3 \$100 \$400 \$500
4 \$100 \$500 \$600
5 \$100 \$600 \$700

I need a formula that will read down the first column to the correct # of users (as specified in another cell), and then across to the correct # of PCs (again, as specified in another cell).

I believe I would be using OFFSET and MATCH for this but cannot get the syntax/logic for it right.
This message was edited by Rylandir on 2002-09-12 11:56

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Use the INDEX Function with the MATCH Function
Book1
ABCDEFGH
1Number of PCs
2123Users3
3Users1\$100\$200\$300PCs2
42\$100\$300\$400
53\$100\$400\$500
64\$100\$500\$600\$ 400
75\$100\$600\$700
...

Doh!

Never mind!

Just realized that OFFSET can take a row AND column parameter within the same formula. That made it easy.

So this formula would work

A1 = 5 (# of users
A2 = 2 (# of PCs)

If the table is laid out starting in B3 then

=OFFSET(B2,MATCH(A1,B4:B8,0),MATCH(A2,C3:C5,0))

Ah, so INDEX would work as well?

Is there an advantage to using INDEX rather than OFFSET (ie. workbook runs faster, etc)? Or is it just a matter of preference?

I seem to recall a posting some time ago by Aladin explaining the difference. I'm not sure, but I believe it had something to do about which one is a Volatile function. I use INDEX because I usually have the data table named and I can use the Range Name instead of the cell references.

Replies
6
Views
606
Replies
5
Views
253
Replies
5
Views
204
Replies
2
Views
132
Replies
3
Views
256

1,218,594
Messages
6,143,383
Members
450,484
Latest member
ChrisMac1

### 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