IFAND formula

[Daithi]

Apologies if this has been covered. I looked at many posts before writing.

I have 2 worksheets, A & B.

I need my formula to look at cell A2 in Sheet B and compare it to Cell A2 in Sheet A. If they match AND the value in sheet A cell B2 is Individual, I want to return the value in Sheet A cell C2.

This is as far as I got.

=IF(AND(A2=SheetA!A2:$A$1999),SheetA!$B$2:$B$2004="Individual",SheetA!$F$2:$F$1999)

Any help gratefully received.

David

 

[AhoyNC]

What you described would be something like this.

I need my formula to look at cell A2 in Sheet B and compare it to Cell A2 in Sheet A. If they match AND the value in sheet A cell B2 is Individual, I want to return the value in Sheet A cell C2.

Excel Workbook
	A	B	C	D
1
2	test	individual	Return Value
3				Return Value
4
5
SheetA

Excel Workbook
	A	B
1
2	test
3
SheetB

However looking at your formula it's unclear.
Where does F2:F1999 come from if you want to return value in C2?
Are you trying to match A2 to a range of values A2:A1999 or just A2 to A2?

 

[Daithi]

Hi

Thanks for the quick reply. Yes, you are correct. My spreadsheet does have 1,999 rows. My plan was to fill the formula down. I notice that I wasn't clear enough in my post.

My formula would be in Sheet B, Cell C2.
It needs to look at Cell A2 in Sheet B and compare this value to Cell A2 in Sheet A.
If they match AND the value in Sheet A Cell B2 is Individual, I want to return the value in Sheet A Cell F2.

Appreciate your patience

 

[AhoyNC]

Copy formula down as needed.

Excel Workbook
	A	B	C
1
2	68		Test1
3	31
4	68
5	36
6	32
7	57
8	93		Test7
9	35
10	54
11	96		Test10
12	43
13	63		Test12
SheetB

Excel Workbook
	A	B
1
2	68	individual
3	31	Test
4	68	Test
5	45	individual
6	32	Test
7	57	Test
8	93	individual
9	35	Test
10	54
11	96	individual
12	43	Test
13	63	individual
SheetA

 

[Daithi]

Brilliant!!!! Thank you so very much.

Copy formula down as needed.

SheetB

	A	B	C
1
2	68		Test1
3	31
4	68
5	36
6	32
7	57
8	93		Test7
9	35
10	54
11	96		Test10
12	43
13	63		Test12

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =IF(AND(A2=SheetA!A2,SheetA!B2="individual"),SheetA!F2,"") <tbody> </tbody>

<tbody>
</tbody>

SheetA

	A	B
1
2	68	individual
3	31	Test
4	68	Test
5	45	individual
6	32	Test
7	57	Test
8	93	individual
9	35	Test
10	54
11	96	individual
12	43	Test
13	63	individual

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:99px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Daithi]

Your solution worked perfectly when the values in Sheet B, Column A are unique. My worksheet has the same value repeated multiple times. I wish I could upload even a screen shot of what I'm doing.

 

[AhoyNC]

One way to post a small sample is to put a boarder around the cells and then use copy / paste to insert into the forum.
What do you want to see when items repeat?

 

[Daithi]

Sheet A

ExampleID	Name Format	Title	First Name	Middle	Last Name	Suffix
Example0001	Business	Mr	John		Jones	OBE
Example0002	Business		J		Robson
Example0003	Family
Example0004	Individual	Dr	John	T	Thompson
Example0005	Individual	Mr & Mrs	John & Mary		Blakely
Example0006	Individual	Prof.	Tom	M	Smith

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet B

ExampleID	Name Format	Last Name
Example0001	Business
Example0002	Business
Example0003	Family
Example0004	Individual	Thompson
Example0005	Individual	Blakely
Example0006	Individual	Smith
Example0006	Individual
Example0006	Individual

<colgroup><col><col><col></colgroup><tbody>
</tbody>

In this example Smith would be returned/repeated

One way to post a small sample is to put a boarder around the cells and then use copy / paste to insert into the forum.
What do you want to see when items repeat?

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C
1	ExampleID	Name Format	Last Name
2	Example0001	Business
3	Example0002	Business
4	Example0003	Family
5	Example0004	Individual	Thompson
6	Example0005	Individual	Blakely
7	Example0006	Individual	Smith
8	Example0006	Individual	Smith
9	Example0006	Individual	Smith
SheetB

Excel Workbook
	A	B	C	D	E	F	G
1	ExampleID	Name Format	Title	First Name	Middle	Last Name	Suffix
2	Example0001	Business	Mr	John		Jones	OBE
3	Example0002	Business		J		Robson
4	Example0003	Family
5	Example0004	Individual	Dr	John	T	Thompson
6	Example0005	Individual	Mr & Mrs	John & Mary		Blakely
7	Example0006	Individual	Prof.	Tom	M	Smith
SheetA

 

[Daithi]

YES!!!!

Thank you so very much

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

SheetB

	A	B	C
1	ExampleID	Name Format	Last Name
2	Example0001	Business
3	Example0002	Business
4	Example0003	Family
5	Example0004	Individual	Thompson
6	Example0005	Individual	Blakely
7	Example0006	Individual	Smith
8	Example0006	Individual	Smith
9	Example0006	Individual	Smith

<colgroup><col style="width:30px; "><col style="width:115px;"><col style="width:108px;"><col style="width:93px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A2&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} C3 {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A3&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} C4 {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A4&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} C5 {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A5&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} C6 {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A6&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} C7 {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A7&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} C8 {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A8&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} C9 {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A9&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

SheetA

	A	B	C	D	E	F	G
1	ExampleID	Name Format	Title	First Name	Middle	Last Name	Suffix
2	Example0001	Business	Mr	John		Jones	OBE
3	Example0002	Business		J		Robson
4	Example0003	Family
5	Example0004	Individual	Dr	John	T	Thompson
6	Example0005	Individual	Mr & Mrs	John & Mary		Blakely
7	Example0006	Individual	Prof.	Tom	M	Smith

<colgroup><col style="width:30px; "><col style="width:110px;"><col style="width:105px;"><col style="width:74px;"><col style="width:93px;"><col style="width:51px;"><col style="width:85px;"><col style="width:43px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4