How Can I ?
 

I am not sure that I can properly describe my question. But here is my best
shot of describing it.
I have a sheet that is Imported Data from a Text file that is about 4K lines
long.
On another sheet I need to return the value of a cell based on multiple tests.
On Sheet2, I need to find the value of C1 in Col C on Sheet1, which is
Duplicated in more than 1 ROW, AND find the value B1 in COL B on Sheet1,
which is also duplicated in more than 1 ROW. However, There will be
ONLY ONE instance of the same combinations and the Row offset will always be
-1.
If the combination is found, Return the Value of COL A.
The example below shows an example of what I am TRYING to describe.

Sheet1
A B C
1 a
2 b
3 c
4 d
5 e
6 c

Sheet2
A B C
1 "Returns" a b c
2 "Returns" d e c
3 "Returns" "" f c

How Can I ?
 

In Sheet2 cell A1 try the below formula and copy down as required. Please
note that this is an array formula. You create array formulas in the same way
that you create other formulas, except you press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

=IF(COUNTA(B1,C1)=2,INDEX(Sheet1!$A$1:$A$10,
MIN(IF((Sheet1!$B$1:$B$10=B1)*
(OFFSET(Sheet1!$B$1:$B$10,1,1)=C1),ROW(Sheet1!$B$1 :$B$10)))-1),"")

If this post helps click Yes
---------------
Jacob Skaria


"Lemmesee" wrote:

I am not sure that I can properly describe my question. But here is my best
shot of describing it.
I have a sheet that is Imported Data from a Text file that is about 4K lines
long.
On another sheet I need to return the value of a cell based on multiple tests.
On Sheet2, I need to find the value of C1 in Col C on Sheet1, which is
Duplicated in more than 1 ROW, AND find the value B1 in COL B on Sheet1,
which is also duplicated in more than 1 ROW. However, There will be
ONLY ONE instance of the same combinations and the Row offset will always be
-1.
If the combination is found, Return the Value of COL A.
The example below shows an example of what I am TRYING to describe.

Sheet1
A B C
1 a
2 b
3 c
4 d
5 e
6 c

Sheet2
A B C
1 "Returns" a b c
2 "Returns" d e c
3 "Returns" "" f c

How Can I ?
 

Thqanks, that works. Can it be modified to handle more than 2 instances of
the same?

"Jacob Skaria" wrote:

In Sheet2 cell A1 try the below formula and copy down as required. Please
note that this is an array formula. You create array formulas in the same way
that you create other formulas, except you press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

=IF(COUNTA(B1,C1)=2,INDEX(Sheet1!$A$1:$A$10,
MIN(IF((Sheet1!$B$1:$B$10=B1)*
(OFFSET(Sheet1!$B$1:$B$10,1,1)=C1),ROW(Sheet1!$B$1 :$B$10)))-1),"")

If this post helps click Yes
---------------
Jacob Skaria


"Lemmesee" wrote:

I am not sure that I can properly describe my question. But here is my best
shot of describing it.
I have a sheet that is Imported Data from a Text file that is about 4K lines
long.
On another sheet I need to return the value of a cell based on multiple tests.
On Sheet2, I need to find the value of C1 in Col C on Sheet1, which is
Duplicated in more than 1 ROW, AND find the value B1 in COL B on Sheet1,
which is also duplicated in more than 1 ROW. However, There will be
ONLY ONE instance of the same combinations and the Row offset will always be
-1.
If the combination is found, Return the Value of COL A.
The example below shows an example of what I am TRYING to describe.

Sheet1
A B C
1 a
2 b
3 c
4 d
5 e
6 c

Sheet2
A B C
1 "Returns" a b c
2 "Returns" d e c
3 "Returns" "" f c