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

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]68[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Test1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]31[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]68[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]36[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]32[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]57[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]93[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Test7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]54[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]96[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Test10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]43[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]63[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Test12[/TD]

</tbody>

Spreadsheet Formulas

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

<tbody>
</tbody>

SheetA

	A	B

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:99px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]68[/TD]
[TD="align: center"]individual[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]31[/TD]
[TD="align: center"]Test[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]68[/TD]
[TD="align: center"]Test[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: center"]individual[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]32[/TD]
[TD="align: center"]Test[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]57[/TD]
[TD="align: center"]Test[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]93[/TD]
[TD="align: center"]individual[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]35[/TD]
[TD="align: center"]Test[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]54[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]96[/TD]
[TD="align: center"]individual[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]43[/TD]
[TD="align: center"]Test[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]63[/TD]
[TD="align: center"]individual[/TD]

</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[TABLE="width: 514"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ExampleID[/TD]
[TD]Name Format[/TD]
[TD]Title[/TD]
[TD]First Name[/TD]
[TD]Middle[/TD]
[TD]Last Name[/TD]
[TD]Suffix[/TD]
[/TR]
[TR]
[TD]Example0001[/TD]
[TD]Business[/TD]
[TD]Mr[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]Jones[/TD]
[TD]OBE[/TD]
[/TR]
[TR]
[TD]Example0002[/TD]
[TD]Business[/TD]
[TD][/TD]
[TD]J[/TD]
[TD][/TD]
[TD]Robson[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Example0003[/TD]
[TD]Family[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Example0004[/TD]
[TD]Individual[/TD]
[TD]Dr[/TD]
[TD]John[/TD]
[TD]T[/TD]
[TD]Thompson[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Example0005[/TD]
[TD]Individual[/TD]
[TD]Mr & Mrs[/TD]
[TD]John & Mary[/TD]
[TD][/TD]
[TD]Blakely[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Example0006[/TD]
[TD]Individual[/TD]
[TD]Prof.[/TD]
[TD]Tom[/TD]
[TD]M[/TD]
[TD]Smith
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Sheet B

[TABLE="width: 343"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ExampleID[/TD]
[TD]Name Format[/TD]
[TD]Last Name[/TD]
[/TR]
[TR]
[TD]Example0001[/TD]
[TD]Business[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Example0002[/TD]
[TD]Business[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Example0003[/TD]
[TD]Family[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Example0004[/TD]
[TD]Individual[/TD]
[TD]Thompson[/TD]
[/TR]
[TR]
[TD]Example0005[/TD]
[TD]Individual[/TD]
[TD]Blakely[/TD]
[/TR]
[TR]
[TD]Example0006[/TD]
[TD]Individual[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]Example0006[/TD]
[TD]Individual[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Example0006[/TD]
[TD]Individual[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

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
Example0001	Business
Example0002	Business
Example0003	Family
Example0004	Individual
Example0005	Individual
Example0006	Individual
Example0006	Individual
Example0006	Individual

<colgroup><col style="width:30px; "><col style="width:115px;"><col style="width:108px;"><col style="width:93px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: #c0c0c0"]ExampleID[/TD]
[TD="bgcolor: #c0c0c0"]Name Format[/TD]
[TD="bgcolor: #c0c0c0"]Last Name[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Thompson[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Blakely[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Smith[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Smith[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Smith[/TD]

</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
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 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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: #c0c0c0"]ExampleID[/TD]
[TD="bgcolor: #c0c0c0"]Name Format[/TD]
[TD="bgcolor: #c0c0c0"]Title[/TD]
[TD="bgcolor: #c0c0c0"]First Name[/TD]
[TD="bgcolor: #c0c0c0"]Middle[/TD]
[TD="bgcolor: #c0c0c0"]Last Name[/TD]
[TD="bgcolor: #c0c0c0"]Suffix[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4