Lookup value and return column offset value

[Tony]

I have the following table that I need to lookup a value. I am trying to use
the =MATCH(G1,LLTable,0) formula to lookup the G1 date 04/03/2009 in the
LLtable below and return the CWW value to the left of each row.

Col "A" Col "B" Col "C"
Col "D"
CWW 1 Mar 16, 2009 Apr 06, 2009 Apr 27, 2009
CWW 2 Mar 20, 2009 Apr 10, 2009 May 01, 2009
CWW 3 Mar 23, 2009 Apr 13, 2009 May 04, 2009
CWW 4 Mar 27, 2009 Apr 17, 2009 May 08, 2009
CWW 5 Mar 30, 2009 Apr 20, 2009 May 11, 2009
CWW 6 Apr 03, 2009 Apr 24, 2009 May 15, 2009

So if the formula above was to work as designed, it would lookup 04/03/2009
in the table and return the corresponding value from column "A" of CWW6.

I am not sure if I should be using a combination MATCH/OFFEST combo. Can
someone guide through this please?

Thanks

Tony

 

[Jim Thomlinson]

As a guess it sounds like you want to use the Index function. It returns the
specified value from a range based on its position in the range...

=index(A2:A100, MATCH(G1,LLTable,0))

FYI vlookup/hlookup can be very problematic to the point where I generally
don't use them anymore. Index/match is a much better solution.

 

[edvwvw via OfficeKB.com]

If you want to check column A using column B as the date source:

=INDEX(A2:A7,MATCH(E2,B2:B7,FALSE))

E2 is where I put the date to query. FALSE within the formula means that the
cell will shown N/A if the date is not in column B - If you leave the FALSE
flag out it will select the exact match or the nearest above.

edvwvw

 

[Tony]

Jim, I have adapted the code as follows:

=INDEX(A1:A6, MATCH(G1,LLTable,0))

but I get a #N/A message. What am I doing wrong? It should return the value
CWW6.

 

[Tony]

I tried this one also, including taking out of the FALSE portion and still
get a #N/A.

 

[Tony]

Okay, so I got around the #N/A problem (MATCH must be in one column only. How
do I write the following code to appear in a VB line and also not show and
show a #N/A response as a ""?

=IF(H$5<>"",INDEX(CWWType, MATCH(H$5,CWWDate,0),1),"")

 

[Jim Thomlinson]

MATCH(H$5,CWWDate,0),1))