Nesting VLOOKUP & HLOOKUP

  • Thread starter Thread starter tuph
  • Start date Start date
T

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
 
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
 
Thanks, Bruce. I've been playing around with this, but can't make i
work. How does it match the place names and dates in both worksheets
 
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
 
Thank you, soooo much. Your formula is exactly what I needed - up to
point, of course!

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

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

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