Using OFFSET for Rows AND Columns? (SOLVED)

Rylandir

New Member
Joined
Feb 18, 2002
Messages
24
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
...
 

Rylandir

New Member
Joined
Feb 18, 2002
Messages
24
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))
 

Rylandir

New Member
Joined
Feb 18, 2002
Messages
24
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?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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.

Aladin?
 

Forum statistics

Threads
1,147,736
Messages
5,742,895
Members
423,760
Latest member
photogfrog

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top