Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match & Offset
I have an interesting problem-one that is confusing me and my collegues. I have a workbook with 2 sheets. One Sheet 1 I have a row of data, first column is data, next column is more data pertaining to the first column. Ex. Column A has a website name and column B has the number of sessions for that site. On sheet 2 I have the exact same dataset, with last weeks data. I need to be able to determine if the data in column A existed last week (and is in the data in the range on sheet 2). If it did, I need to return the value in the number of sessions column for last week (sheet 2). I am close, but cannot figure out how to use offset because I don't know what to use as the reference in this case. =IF(ISNA(MATCH(C57,Sheet2!$A$1:$A$5,0)),"blank",OF FSET(C57,0,4,1,1)) The value for C57 above needs to be the value of column A on sheet 2. Does this manke any sense? Thanks a ton! Best, Nathan P.S. Let me know if seeing an example would help. -- longhorn14 ------------------------------------------------------------------------ longhorn14's Profile: http://www.excelforum.com/member.php...o&userid=33275 View this thread: http://www.excelforum.com/showthread...hreadid=530995 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match & Offset
Have you considered using VLookup?
=IF(ISNA(VLOOKUP(C57,Sheet2!$A$1:$B$5,2,0)),"Blank ",VLOOKUP(C57,Sheet2!$A$1:$B$5,2,0)) "longhorn14" wrote: I have an interesting problem-one that is confusing me and my collegues. I have a workbook with 2 sheets. One Sheet 1 I have a row of data, first column is data, next column is more data pertaining to the first column. Ex. Column A has a website name and column B has the number of sessions for that site. On sheet 2 I have the exact same dataset, with last weeks data. I need to be able to determine if the data in column A existed last week (and is in the data in the range on sheet 2). If it did, I need to return the value in the number of sessions column for last week (sheet 2). I am close, but cannot figure out how to use offset because I don't know what to use as the reference in this case. =IF(ISNA(MATCH(C57,Sheet2!$A$1:$A$5,0)),"blank",OF FSET(C57,0,4,1,1)) The value for C57 above needs to be the value of column A on sheet 2. Does this manke any sense? Thanks a ton! Best, Nathan P.S. Let me know if seeing an example would help. -- longhorn14 ------------------------------------------------------------------------ longhorn14's Profile: http://www.excelforum.com/member.php...o&userid=33275 View this thread: http://www.excelforum.com/showthread...hreadid=530995 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match & Offset
JMB wrote...
Have you considered using VLookup? =IF(ISNA(VLOOKUP(C57,Sheet2!$A$1:$B$5,2,0)),"Blan k", VLOOKUP(C57,Sheet2!$A$1:$B$5,2,0)) While the OP's OFFSET call should be replaced with a VLOOKUP call, the OP's MATCH call is a better idea than your first VLOOKUP call because there's a possibility that the value referenced by VLOOKUP could itself be #N/A. There's a strong argument for returning such #N/A values. The OP's MATCH call would only return #N/A if C57 weren't found in Sheet2!A1:B5. As for your second VLOOKUP call, the OP seems to want the 4th column to the right of column A, i.e., column E, not column B. So, better to use =IF(ISNA(MATCH(C57,Sheet2!$A$1:$A$5,0)),"blank", VLOOKUP(C57,Sheet2!$A$1:$E$5,5,0)) "longhorn14" wrote: .... One Sheet 1 I have a row of data, first column is data, next column is more data pertaining to the first column. Ex. Column A has a website name and column B has the number of sessions for that site. On sheet 2 I have the exact same dataset, with last weeks data. I need to be able to determine if the data in column A existed last week (and is in the data in the range on sheet 2). If it did, I need to return the value in the number of sessions column for last week (sheet 2). I am close, but cannot figure out how to use offset because I don't know what to use as the reference in this case. =IF(ISNA(MATCH(C57,Sheet2!$A$1:$A$5,0)),"blank", OFFSET(C57,0,4,1,1)) .... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match & Offset
That's a good point. I actually did consider using Match for the first
argument, but I had not thought it through as far as you did that the value being returned could itself be #N/A. Thanks for the corrections. "Harlan Grove" wrote: JMB wrote... Have you considered using VLookup? =IF(ISNA(VLOOKUP(C57,Sheet2!$A$1:$B$5,2,0)),"Blan k", VLOOKUP(C57,Sheet2!$A$1:$B$5,2,0)) While the OP's OFFSET call should be replaced with a VLOOKUP call, the OP's MATCH call is a better idea than your first VLOOKUP call because there's a possibility that the value referenced by VLOOKUP could itself be #N/A. There's a strong argument for returning such #N/A values. The OP's MATCH call would only return #N/A if C57 weren't found in Sheet2!A1:B5. As for your second VLOOKUP call, the OP seems to want the 4th column to the right of column A, i.e., column E, not column B. So, better to use =IF(ISNA(MATCH(C57,Sheet2!$A$1:$A$5,0)),"blank", VLOOKUP(C57,Sheet2!$A$1:$E$5,5,0)) "longhorn14" wrote: .... One Sheet 1 I have a row of data, first column is data, next column is more data pertaining to the first column. Ex. Column A has a website name and column B has the number of sessions for that site. On sheet 2 I have the exact same dataset, with last weeks data. I need to be able to determine if the data in column A existed last week (and is in the data in the range on sheet 2). If it did, I need to return the value in the number of sessions column for last week (sheet 2). I am close, but cannot figure out how to use offset because I don't know what to use as the reference in this case. =IF(ISNA(MATCH(C57,Sheet2!$A$1:$A$5,0)),"blank", OFFSET(C57,0,4,1,1)) .... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match & Offset
I went ahead and used vlookup and it worked great. Not sure why I didn't think of that! Thanks again! -- longhorn14 ------------------------------------------------------------------------ longhorn14's Profile: http://www.excelforum.com/member.php...o&userid=33275 View this thread: http://www.excelforum.com/showthread...hreadid=530995 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Index - Offset - Match Issues | Excel Worksheet Functions | |||
Complicated Index Match Offset function | Excel Worksheet Functions | |||
Formula Help With MATCH & OFFSET | Excel Worksheet Functions | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions |