| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value and return column offset value
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup value and return column offset value
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. -- HTH... Jim Thomlinson "Tony" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup value and return column offset value
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 wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200904/1 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup value and return column offset value
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. "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "Tony" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup value and return column offset value
I tried this one also, including taking out of the FALSE portion and still
get a #N/A. "edvwvw via OfficeKB.com" wrote: 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 wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200904/1 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup value and return column offset value
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),"") "Tony" wrote: I tried this one also, including taking out of the FALSE portion and still get a #N/A. "edvwvw via OfficeKB.com" wrote: 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 wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200904/1 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup value and return column offset value
MATCH(H$5,CWWDate,0),1))
-- HTH... Jim Thomlinson "Tony" wrote: 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),"") "Tony" wrote: I tried this one also, including taking out of the FALSE portion and still get a #N/A. "edvwvw via OfficeKB.com" wrote: 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 wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200904/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find max value in column and return offset of that value | Excel Discussion (Misc queries) | |||
| LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
| Lookup a row and column and return value | Excel Worksheet Functions | |||
| offset 1 row and return to first column | Excel Discussion (Misc queries) | |||
| lookup from one column return value from another? | Excel Discussion (Misc queries) | |||
Linear Mode
