2 way lookup with varying row/column headers

[nickwilshaw]

Hi All,

Guess the title sums it up but need to have a macro that will return a value dependent on the row and column.

Accept a sime Index/Match/Match set up will handle this, however, the row headers and columns might be in different row and columns as the sheets from which the data is called are not consistent.

For example, on one spreadsheet for a particular series of months, the dates are in columns B-J but in another it is C-K (appreciate that you would normally have the dates in rows, respresenting the x-axis but the sheets concern projections......)

Likewise, the named accounts might be in the first row, or second row.

I have covered this so far by using an iferror arrangement and using either B-J or C-K. However, I wondered if there was a more succinct way of doing this.

Many thanks,


Nick

 

[Sandeep Warrier]

Hi

It'll be better if you could provide some sample data.

Some ways of displaying data on the board are mentioned in my signature.

 

[nickwilshaw]

OK - to give further details.......

I need to poulate a table with data drawn from a list of accounts.

Typically the account name will be in Column A and the value in Column B and a simple index/match will return the required data

ie in a document called Jan accounts in a folder called 2008, I might have something like this

Account Name Balance

Sales 1,000,000

Cost of Goods Sold 700,000

Gross Profit 300,000

and I want a spreadsheet that looks like this

Month Gross Profit
Jan 300,000
Feb 250,000
Mar 200,000 etc

so, as I said, the index/match will do this easily

Now, say for example we change the format in 2009 so the original spreadsheet looks like this:

Account Centre Amount
Sales US 500,000
UK 200,000

Cost of Goods Sold US 350,000
UK 150,000

Gross Profit US 150,000
UK 50,000

The problem now is that I need to index match a different range so I would need to find some way of writing a generic formula/macro that will not define the range so it covers all the columns and performs that index/match on the relevant column/rows not a pre-defined range that may or may not fit tie criteria.

Hope this helps a bit