Lookup with multiple criteria on diff worksheets

[goss]

Hi ng.
Using xl xp pro

Trying to setup some lookup formulas on summary!
Data to be queried is on data!

Data is setup in this format:
Col A - Unit (15 but may need to add more)
Row 1 - Account (20 but may need to add more)
Current range defined as My_Range

Summary is setup in this format:
$d$1:$R$1 - Unit
$B$2:$B$32 - Account

Units and accounts are always text format. Ex Unit = Arizona , Account =
Phoenix A Unit may have many different accounts

Trying to setup fomula on summary! so when find matching criteria D1 and B2
return intersecting value from Data!

Then when drag formula right or down update criteria for new intersecting
values.

tia,
goss

 

[Debra Dalgleish]

If you're able to rearrange your data worksheet, you could use a pivot
table to summarize the data. It would automatically include new accounts
or units as the pivot table is refreshed.

The data could be rearranged as follows:

Unit Acct Amt
Unit1 Acct1 10
Unit2 Acct1 30
Unit1 Acct2 20


Then, create a pivot table with Account in the Row area, Unit in the
column area, and Amount in the data area.

To restructure your data table, you may be able to use the "unpivot'
technique described by John Walkenbach:
http://j-walk.com/ss/excel/usertips/tip068.htm

Then change the headings, e.g. Unit, Acct, Amt