Using OFFSET for Rows AND Columns? (SOLVED)

[Rylandir]

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

 

[lenze]

Use the INDEX Function with the MATCH Function

Book1
	A	B	C	D	E	F	G	H
1			Number of PCs
2			1	2	3		Users	3
3	Users	1	$100	$200	$300		PCs	2
4		2	$100	$300	$400
5		3	$100	$400	$500
6		4	$100	$500	$600		$ 400
7		5	$100	$600	$700
...

 

[Rylandir]

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]

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]

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?