Nesting VLOOKUP & HLOOKUP

[tuph]

I have a table with dates in row 1 and city names in column A, plus another worksheet which contains the data I want to use in the main worksheet.

I can do a VLOOKUP on the place names in column A to return the correct result by specifying the column number in the array; and I can do an HLOOkUP on the dates in row 1 and return the correct result by specifying the row number in that array.

How can I nest these and refer to both the date and the place name and return the desired result?

Tuph in Melbourne, Australia

[bpeltzer]

Once you know the row and column number, you can use indirect and address:
=indirect(address(4,5,,,"Sheet2")), for instance, returns the value in
Sheet2!E4.
(You could also use the offset function: offset(Sheet2!$A$1,row-1,column-1)
--Bruce


"tuph" wrote:

>
> I have a table with dates in row 1 and city names in column A, plus
> another worksheet which contains the data I want to use in the main
> worksheet.
>
> I can do a VLOOKUP on the place names in column A to return the correct
> result by specifying the column number in the array; and I can do an
> HLOOkUP on the dates in row 1 and return the correct result by
> specifying the row number in that array.
>
> How can I nest these and refer to both the date and the place name and
> return the desired result?
>
> Tuph in Melbourne, Australia
>
>
> --
> tuph
> ------------------------------------------------------------------------
> tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
> View this thread: http://www.excelforum.com/showthread...hreadid=510876
>
>

[tuph]

Thanks, Bruce. I've been playing around with this, but can't make it work. How does it match the place names and dates in both worksheets?

[JMB]

Are you trying to do a two-dimensional lookup?

http://www.j-walk.com/ss/excel/usertips/tip020.htm

You could also use the Match function for the third argument of Vlookup.
Match returns the index number of the match found, so you could use it to
match the date you're looking for to the dates in the first row of your table.

"tuph" wrote:

>
> I have a table with dates in row 1 and city names in column A, plus
> another worksheet which contains the data I want to use in the main
> worksheet.
>
> I can do a VLOOKUP on the place names in column A to return the correct
> result by specifying the column number in the array; and I can do an
> HLOOkUP on the dates in row 1 and return the correct result by
> specifying the row number in that array.
>
> How can I nest these and refer to both the date and the place name and
> return the desired result?
>
> Tuph in Melbourne, Australia
>
>
> --
> tuph
> ------------------------------------------------------------------------
> tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
> View this thread: http://www.excelforum.com/showthread...hreadid=510876
>
>

[bpeltzer]

Try match: =match("Melbourne",Sheet2!A:A,false) will tell you the row number
where Melbourne is first found in column A. Likewise
=match(date(2006,1,1),Sheet2!1:1,false) will tell you the first column
labeled 1/1/06. So those would be your row and column numbers.
In total:
=indirect(address(match("Melbourne",Sheet2!A:A,false),match(date(2006,1,1),Sheet2!1:1,false),,,"Sheet2"))
--BP

"bpeltzer" wrote:

> Once you know the row and column number, you can use indirect and address:
> =indirect(address(4,5,,,"Sheet2")), for instance, returns the value in
> Sheet2!E4.
> (You could also use the offset function: offset(Sheet2!$A$1,row-1,column-1)
> --Bruce
>
>
> "tuph" wrote:
>
> >
> > I have a table with dates in row 1 and city names in column A, plus
> > another worksheet which contains the data I want to use in the main
> > worksheet.
> >
> > I can do a VLOOKUP on the place names in column A to return the correct
> > result by specifying the column number in the array; and I can do an
> > HLOOkUP on the dates in row 1 and return the correct result by
> > specifying the row number in that array.
> >
> > How can I nest these and refer to both the date and the place name and
> > return the desired result?
> >
> > Tuph in Melbourne, Australia
> >
> >
> > --
> > tuph
> > ------------------------------------------------------------------------
> > tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
> > View this thread: http://www.excelforum.com/showthread...hreadid=510876
> >
> >

[tuph]

Thank you, soooo much. Your formula is exactly what I needed - up to a point, of course!

In my summary worksheet I now have an INDIRECT formula which looks up a location from col A and a date from row 5, and matches them with an array in a separate data worksheet, returning the correct result. So far, so good. BUT -

In looking more closely at the data tables, I find that I have several arrays which contain the same date range, but only one column containing the locations - column A. For example, I have data for each month of the financial year for Sales, Budget, Staff Levels, etc. These all run across the worksheet from left to right. Each array has it's own column headings, which are specific to the data in the array, and these headings are matched on the current month columns in the summary worksheet.

My question is this: Can I have a 3-way lookup that addresses the heading (picking up the array containing all columns with that heading), the date, and the location? [Just in case you have nothing else to do )]