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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,328
Messages
5,571,564
Members
412,405
Latest member
DutchMonkey
Top