Using Match & Offset

  • Thread starter Thread starter longhorn14
  • Start date Start date
L

longhorn14

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",OFFSET(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.
 
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))
 
JMB wrote...
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))
Click to expand...

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: ....
Click to expand...
....
 
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.
 
I went ahead and used vlookup and it worked great. Not sure why I didn't
think of that!
Thanks again!
 
Back
Top