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

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

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

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

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