Combining hlookup and vlookup

Hi Shane

The values representing industries start B8 on your master sheet, right?

Are the other sheets all similar in structure? If so, how are they organized?

Aladin

hlookup and vlookup..sim. to recent

Mark W posted this one: a matrix of
....... small medium large
A 32 43 35
AA 39 16 44
AAA 16 44 26
B 33 32 37
and simple formula of
=AAA large

Shane:

I think combining the INDEX() and MATCH() functions will get you what you need.
the formula would be something like:

=INDEX($B$7:$Z$60,MATCH(industry,$B$7:$B$60,0),MATCH(month,$B$7:$Z$7,0))

HTH

Mark

Aladin,

all the source worksheets (not the master worksheet) are the same format (i.e. - industries as row headers in A2, A3, etc. and dates as column headers in B2, C2, etc. with dollar amounts in the body of the table. The dollar amounts represent different things depending on which worksheet they come from. The master has the same industry row headers, but I want to be able to have a column representing each of the source worksheets. I then want to be able to put a date in the column header and have a formula pull information for the corresponding date and industry.
Thanks!

Querying a source sheet

: I have a spreadsheet with multiple worksheets. I have a master worksheet with variables in the B column that represent industries (retail, autos, etc) and variables in row 7 that represent dates (Dec00, Sep00, etc). I need a formula that will act somewhat like a 2 variable query that will find the amount on a prescribed worksheet that matches the two variables (e.g. amount for 'retail' as of 'Dec00'). I tried to nest a vlookup in an hlookup but had no success. Any advice?

I'll assume that you are not trying simply to copy a given source sheet to the master sheet. Rather, given a date (actually a month) and an industry, you want to retrieve the relevant amount from a particular source sheet, that is, querying a source sheet. Right?

If so, I propose that you insert another sheet and name it BLACKBOARD.

On BLACKBOARD, create 3 lists, say in columns A, B, and C.
Enter the names of the source sheets in A from A1 on. Select the cells the names occupy and name the selection SOURCES via the Name Box.
Enter the possible dates in B from B1 on, exactly the way they appear on the source sheets. Again select the cells with these values and name it MONTHS or DATES.
Enter the industries exactly the way they appear on the source sheets in C from C1 on and name the range occupied by them INDUSTRIES.

On the master sheet:

Activate A2, select the option Data|Validation, choose List for Allow and type =SOURCES for Source on Settings tab.

Activate B2, select the option Data|Validation, choose List for Allow and type =INDUSTRIES for Source on Settings tab.

Activate C2, select the option Data|Validation, choose List for Allow and type =MONTHS for Source on Settings tab.

Activate A2 and name it Source via the Name Box.
Activate B2 and name it Industry via the Name Box.
Activate C2 and name it Month via the Name Box.

I'll assume the following structure concerning the source sheets.

A2:A10 contains the industries, B1:G1 the months, and B2:G10 the amounts.

Enter in A4 (still on the Master sheet) the following formula:

=INDEX(INDIRECT(CONCATENATE(Source,"!","$B$2:$G$11")),MATCH(Industry,INDIRECT(CONCATENATE(Source,"!","$A$2:$A$11")),0),MATCH(Month,INDIRECT(CONCATENATE(Source,"!","$B$1:$G$1")),0))

This will give you an amount from a particular source sheet associated with a particular industry in a particular month.

I hope I didn't try to solve the wrong problem.

Aladin

Re: Querying a source sheet

: