Nesting VLOOKUP & HLOOKUP
 

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

Nesting VLOOKUP & HLOOKUP
 

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

Nesting VLOOKUP & HLOOKUP
 

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?


--
tuph
------------------------------------------------------------------------
tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=510876

Nesting VLOOKUP & HLOOKUP
 

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

Nesting VLOOKUP & HLOOKUP
 

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,fal se),match(date(2006,1,1),Sheet2!1:1,false),,,"Shee t2"))
--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

Nesting VLOOKUP & HLOOKUP
 

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 :))]


--
tuph
------------------------------------------------------------------------
tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=510876