Desperately Seeking Offset Formula!!
 

Hi All,
I have a Workbook with a Main Sheet ("Main Page") Cell O6 referencing a
certain cell value. This cell contains the Last Name I'm looking to match on
another page. The next page "Test Results Data" cells A2:A21 contain the
needed look-up Last Name. I also need to match another cell along with the
Last Name (would come in the same row as last name). Cells C2:C21 which is
the Employee Number are also needed to confirm last name validity.

I've started by using the following formula on the "Main Page" which works
fine - - - =IF(MATCH(O6,'Test Results Data'!A2:A21,0)<"","Yes",""). I'm still
in need of another part (meaning Offset Formula) to have this also look 2
Cells to the Right to see if corresponding cells C2:C21 also has a value <"".

Any help would be greatly appreciated. I've come close, but I can not quite
get it and I'm close to ripping out patches of hair trying to finally figure
this one out!!!

Thanks in advance - Jenny B .

Desperately Seeking Offset Formula!!
 

Jenny,
I think you are almost there... The Match function gives you the row number
where the lastname is found in the reference range. Ie. MATCH(O6,'Test
Results Data'!A2:A21,0) will give you the rownumber in range A2:A21 if there
is a match, if there is no match then you get #N/A.
Using that same number you can use =Offset('Test Results
Data'!C1,MATCH(O6,'Test Results Data'!A2:A21,0),0) will give you your answer
- but it gives an #N/A when the name is not found. Note that I started with
cell C1 in the formula, as your data starts in row 2 (ie if found in the
first row it looks down 1 row from C1 to arrive at C2).
Another way to arrive at the C column is to start at cell A1 and use a "2"
in the end of the formula, ie 2 to the right lands you at column C
=Offset('Test Results Data'!A1,MATCH(O6,'Test Results Data'!A2:A21,0),2)

rdwj

"Jenny B." wrote:

Hi All,
I have a Workbook with a Main Sheet ("Main Page") Cell O6 referencing a
certain cell value. This cell contains the Last Name I'm looking to match on
another page. The next page "Test Results Data" cells A2:A21 contain the
needed look-up Last Name. I also need to match another cell along with the
Last Name (would come in the same row as last name). Cells C2:C21 which is
the Employee Number are also needed to confirm last name validity.

I've started by using the following formula on the "Main Page" which works
fine - - - =IF(MATCH(O6,'Test Results Data'!A2:A21,0)<"","Yes",""). I'm still
in need of another part (meaning Offset Formula) to have this also look 2
Cells to the Right to see if corresponding cells C2:C21 also has a value <"".

Any help would be greatly appreciated. I've come close, but I can not quite
get it and I'm close to ripping out patches of hair trying to finally figure
this one out!!!

Thanks in advance - Jenny B .

Desperately Seeking Offset Formula!!
 

Your question is not too clear.

This formula will give you a "YES" if there *is* a number in Column C of the
corresponding row where the name was found,

A "No ID Num" text message, if a name was found without a corresponding
number in Column C,

And a #N/A error if *no* matching name was found:

=IF(AND(MATCH(O6,A2:A21,0),INDEX(A2:C21,MATCH(O6,A 2:A21,0),3)<""),"YES","No
ID Num")


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Jenny B." wrote in message
...
Hi All,
I have a Workbook with a Main Sheet ("Main Page") Cell O6 referencing a
certain cell value. This cell contains the Last Name I'm looking to match
on
another page. The next page "Test Results Data" cells A2:A21 contain the
needed look-up Last Name. I also need to match another cell along with
the
Last Name (would come in the same row as last name). Cells C2:C21 which
is
the Employee Number are also needed to confirm last name validity.

I've started by using the following formula on the "Main Page" which works
fine - - - =IF(MATCH(O6,'Test Results Data'!A2:A21,0)<"","Yes",""). I'm
still
in need of another part (meaning Offset Formula) to have this also look 2
Cells to the Right to see if corresponding cells C2:C21 also has a value
<"".

Any help would be greatly appreciated. I've come close, but I can not
quite
get it and I'm close to ripping out patches of hair trying to finally
figure
this one out!!!

Thanks in advance - Jenny B .

Desperately Seeking Offset Formula!!
 

Thank you, Thank you, Thank you!

It make so much sense and seems so obvious, but it must take a sharper tool
in the drawer then myself to sometimes reason these things out.

Thank you again for all of your help - Jenny B.

"rdwj" wrote:

Jenny,
I think you are almost there... The Match function gives you the row number
where the lastname is found in the reference range. Ie. MATCH(O6,'Test
Results Data'!A2:A21,0) will give you the rownumber in range A2:A21 if there
is a match, if there is no match then you get #N/A.
Using that same number you can use =Offset('Test Results
Data'!C1,MATCH(O6,'Test Results Data'!A2:A21,0),0) will give you your answer
- but it gives an #N/A when the name is not found. Note that I started with
cell C1 in the formula, as your data starts in row 2 (ie if found in the
first row it looks down 1 row from C1 to arrive at C2).
Another way to arrive at the C column is to start at cell A1 and use a "2"
in the end of the formula, ie 2 to the right lands you at column C
=Offset('Test Results Data'!A1,MATCH(O6,'Test Results Data'!A2:A21,0),2)

rdwj

"Jenny B." wrote:

Hi All,
I have a Workbook with a Main Sheet ("Main Page") Cell O6 referencing a
certain cell value. This cell contains the Last Name I'm looking to match on
another page. The next page "Test Results Data" cells A2:A21 contain the
needed look-up Last Name. I also need to match another cell along with the
Last Name (would come in the same row as last name). Cells C2:C21 which is
the Employee Number are also needed to confirm last name validity.

I've started by using the following formula on the "Main Page" which works
fine - - - =IF(MATCH(O6,'Test Results Data'!A2:A21,0)<"","Yes",""). I'm still
in need of another part (meaning Offset Formula) to have this also look 2
Cells to the Right to see if corresponding cells C2:C21 also has a value <"".

Any help would be greatly appreciated. I've come close, but I can not quite
get it and I'm close to ripping out patches of hair trying to finally figure
this one out!!!

Thanks in advance - Jenny B .