Using IF/IF Error to Label Values

Joined
Aug 30, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • Data.JPG
    Data.JPG
    70 KB · Views: 8
  • Formula.JPG
    Formula.JPG
    15.1 KB · Views: 8

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hii

Welcome to MrExcel!

What the VLOOKUP formula stand alone returns??
 
Upvote 0
Or you want something like:

Book1
ABCDEFGHIJKLMNOPQRST
1
2ReportAttendance
3
4NoWelcome DayLunch MeetingWelcome Day AttendanceLunch Meeting Attendance
51AttendedNot Attended17
62AttendedNot Attended28
73AttendedNot Attended39
84AttendedNot Attended4
95AttendedNot Attended5
106Not AttendedNot Attended
117Not AttendedAttended
128Not AttendedAttended
139Not AttendedAttended
1410Not AttendedNot Attended
1511Not AttendedNot Attended
16
17
18
19
20
21
22
23
24
Sheet1
Cell Formulas
RangeFormula
B5:B15B5=IF(ISNUMBER(IFERROR(INDEX($R$5:$R$9,MATCH(A5,$R$5:$R$9,0)),"")),"Attended","Not Attended")
C5:C15C5=IF(ISNUMBER(IFERROR(INDEX($S$5:$S$9,MATCH(A5,$S$5:$S$9,0)),"")),"Attended","Not Attended")
 
Upvote 0
Hi & welcome to MrExcel.
Another option
Excel Formula:
=LET(v,VLOOKUP(B4,$R$4:$S$9,2,0),IF(ISNA(v),"Not attended","Attended"))
 
Upvote 0
Or you want something like:

Book1
ABCDEFGHIJKLMNOPQRST
1
2ReportAttendance
3
4NoWelcome DayLunch MeetingWelcome Day AttendanceLunch Meeting Attendance
51AttendedNot Attended17
62AttendedNot Attended28
73AttendedNot Attended39
84AttendedNot Attended4
95AttendedNot Attended5
106Not AttendedNot Attended
117Not AttendedAttended
128Not AttendedAttended
139Not AttendedAttended
1410Not AttendedNot Attended
1511Not AttendedNot Attended
16
17
18
19
20
21
22
23
24
Sheet1
Cell Formulas
RangeFormula
B5:B15B5=IF(ISNUMBER(IFERROR(INDEX($R$5:$R$9,MATCH(A5,$R$5:$R$9,0)),"")),"Attended","Not Attended")
C5:C15C5=IF(ISNUMBER(IFERROR(INDEX($S$5:$S$9,MATCH(A5,$S$5:$S$9,0)),"")),"Attended","Not Attended")

Or you want something like:

.
 
Upvote 0
Hi & welcome to MrExcel.
Another option
Excel Formula:
=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 :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top