Lookups: Three way match?

[Julie]

Hi,

I have a lookup table that looks like this (only wider and longer -
lol):

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.)
called MyLookupCodes.

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
=HLOOKUP(E$1,MyLookupTable,MATCH(A2,MyLookupCodes,0),TRUE)
....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
the dates.)

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
workbook.

Excel 2003, btw.

Help?

Thanks for any guidance.

Julie

 

[Herbert Seidenberg]

Use Lookup()
http://www.freefilehosting.net/download/3j7fk

 

[Julie]

Use Lookup()http://www.freefilehosting.net/download/3j7fk

Unfortunately, Lookup won't work either. I could build a range,
because I know the row number and the width of the lookup table, but
it won't let me offset from the found value (the date) so that I can
get the corresponding value (the single letter), which is the same
problem that I have with the formula listed in my original post.

I think I need a way to retrieve the column number from the lookup
table so I can offset it by 10, and I don't think there's a way to do
that.

Thanks anyway!

Julie

 

[Julie]

And duh - I just noticed the link. Looking over this now...not sure
what you're doing, but I hope to heck it works! Sorry, I'm an idiot
this morning. Too many fireworks.

Thanks!,

Julie

 

[Julie]

Nope, unfortunately it won't work for this application...I see what
you're doing, naming all of the ranges by the 2-letter code in the
left column and creating a second range for each code in the right
column, but he's going to be adding these on the fly as part of a much
larger project...if *I* were doing it, I could do this, but the
potential for things getting messed up having him name each row is
just too great.

Maybe I'll write a VBA routine or something that does the range
naming.

(Still haven't lost all hope of finding a formula - lol - I am already
riddling this spreadsheet with VBA, so I'm trying to do as much as
possible with formulas.)

Thanks,

Julie Siebel

 

[Julie]

Aha! Thanks so much. Since I can get the row using MATCH in the
formula above, and the width of my table is fixed, I can use OFFSET to
create the ranges for your vector LOOKUP commands, where you were
using named ranges.

(Just figured I'd include this information in case someone was
searching for an answer to a similar problem.)

Thank you VERY much Herbert...I really appreciate it

Julie