|Subject:||Lookups: Three way match?|
|Posted by:||Julie (julie.sieb…@gmail.com)|
|Date:||Thu, 3 Jul 2008|
I have a lookup table that looks like this (only wider and longer -
AA 24 Dec 2008 23 Mar 2009 25 May 2009 A B C
BB 5 Feb 2009 1 Jul 2009 <blank> C B <blank>
CC 2 Jan 2009 12 Feb 2009 <blank> C A <blank>
AA 21 Jan 2009 17 May 2009 31 Aug 2009 B A C
I need a letter from the A/C/B columns.
In the worksheet where I'm doing the lookups, I have the double letter
codes in the left column, and a series of dates above in the top row.
What I need is the A B or C (or whichever letter I end up with) at the
end of the lookup table.
Assume that the entire table is called MyLookupTable, and there's
another named range that includes the first column (AA, BB, CC etc.)
In the worksheet I'm putting the formula in, Column A holds a list of
double letter codes, and Row 1 holds a list of dates.
I got this far
....which gives me the date, but I need the value offset horizontally
three (in the example) spaces to the right. I can't figure out a way
to get the column for the lookup so I can just offset it three places.
So if I'm looking up CC for Jan 31 2009, I want it to return the A
from that row.
Is there any way to do this?
I can change the table(s) around somewhat, but the guy who will be
using it will be adding lots of double letter codes to the table with
dates to the table, and in the real version, there will be 10 spaces
for dates going across in the lookup table and that won't change. (So
the single letter codes will always be offset by the same amount from
We want to add data to the lookup table in rows as opposed to columns,
so he can see several complete sets of data at the same time. Also, in
other places in the workbook we *do* need the date from the same table
(using the formula listed above). So I guess the format can't change
*too* much. I tried to make it work interspersing the dates with the
letter codes (e.g. date C date A date B) but that didn't work either.
I have a sheet of constants and variables, and used
cell("address",MyLookupTable) figuring I could BUILD the range using
Indirect(), since I know the width of the table, then use match on
that...but the Cell function yielded the full [workbook]worksheetname!
$A$1 cell reference rather than just $A$1 like the help files say, and
I couldn't figure out how to make it work in a formula.
What am I missing here? I've played with every function I can think of
that could possibly apply (Cell, Vlookup, Lookup, Offset, Address...a
bunch more). I'm feeling brain dead at this point; it can't be as hard
as I'm making it!
Oh, and if it helps (don't think it will, because the dates in the
lookup table have to be entered as dates... but in the interest of
completeness) the sheet where I'm *doing* the lookup with the dates
across the top...those are all consecutive Wednesdays from 31 Dec 2008
through the 24th. It's one of the few regular and static parts of the
Excel 2003, btw.
Thanks for any guidance.