Using IF/IF Error to Label Values

[Data can be tough AP]

Hi all,

I am trying to quickly clean up some data using the IF functions and cannot seem to get where I want. I have a dataset, where I am using a vlookup to find a function if true, and if successful I would like to the TRUE value to return a certain label, and if FALSE another one of course. The problem is I cannot seem to get the formula to return any value except for the value the look up function is finding. Ideally I wanted to use the IFERROR as well to clean up the NAs but not sure if this is also possible in one go.

Is there any advice you can give me? I've attached a mock sheet just in case.

Best regards!

 

[Sufiyan97]

Hii

Welcome to MrExcel!

What the VLOOKUP formula stand alone returns??

 

[Sufiyan97]

Or you want something like:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Report																	Attendance
3
4	No	Welcome Day	Lunch Meeting															Welcome Day Attendance	Lunch Meeting Attendance
5	1	Attended	Not Attended															1	7
6	2	Attended	Not Attended															2	8
7	3	Attended	Not Attended															3	9
8	4	Attended	Not Attended															4
9	5	Attended	Not Attended															5
10	6	Not Attended	Not Attended
11	7	Not Attended	Attended
12	8	Not Attended	Attended
13	9	Not Attended	Attended
14	10	Not Attended	Not Attended
15	11	Not Attended	Not Attended
16
17
18
19
20
21
22
23
24
Sheet1

Cell Formulas
Range	Formula
B5:B15	B5	=IF(ISNUMBER(IFERROR(INDEX($R$5:$R$9,MATCH(A5,$R$5:$R$9,0)),"")),"Attended","Not Attended")
C5:C15	C5	=IF(ISNUMBER(IFERROR(INDEX($S$5:$S$9,MATCH(A5,$S$5:$S$9,0)),"")),"Attended","Not Attended")

 

[Fluff]

Hi & welcome to MrExcel.
Another option

=LET(v,VLOOKUP(B4,$R$4:$S$9,2,0),IF(ISNA(v),"Not attended","Attended"))

 

[Data can be tough AP]

Or you want something like:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Report																	Attendance
3
4	No	Welcome Day	Lunch Meeting															Welcome Day Attendance	Lunch Meeting Attendance
5	1	Attended	Not Attended															1	7
6	2	Attended	Not Attended															2	8
7	3	Attended	Not Attended															3	9
8	4	Attended	Not Attended															4
9	5	Attended	Not Attended															5
10	6	Not Attended	Not Attended
11	7	Not Attended	Attended
12	8	Not Attended	Attended
13	9	Not Attended	Attended
14	10	Not Attended	Not Attended
15	11	Not Attended	Not Attended
16
17
18
19
20
21
22
23
24
Sheet1

Cell Formulas
Range	Formula
B5:B15	B5	=IF(ISNUMBER(IFERROR(INDEX($R$5:$R$9,MATCH(A5,$R$5:$R$9,0)),"")),"Attended","Not Attended")
C5:C15	C5	=IF(ISNUMBER(IFERROR(INDEX($S$5:$S$9,MATCH(A5,$S$5:$S$9,0)),"")),"Attended","Not Attended")

Or you want something like:

.

 

[Data can be tough AP]

This is really helpful and worked just perfectly now need to just get my head around it!

 

[Data can be tough AP]

Hi & welcome to MrExcel.
Another option

=LET(v,VLOOKUP(B4,$R$4:$S$9,2,0),IF(ISNA(v),"Not attended","Attended"))

This worked the trick, uses some elements I would have never thought to try so need to try and learn this

 

[Fluff]

Glad we could help & thanks for the feedback.