When a value in an array is missing, returning ""

jcarroll01

Board Regular
Joined
Jun 17, 2005
Messages
122
I have a formula that looks up a value in a column based on finding "lunch" in another column....I need to find a way to return another value if the "Lunch" line item is missing.
Macro-Master.xls
ABCDEFGHIJKL
1624573Ahsan,SofiaDate:06/06-MayLunchAdh.
17IDAdh.%
18--------Adherence---------245730.23
19ScheduledScheduledActualMin.InMin.OutPerc.In247311
20ActivitiesTimeTimeAdhereAdhereAdhere27428#N/A
21--------------------------------------------------------------212120.83
22Sign-on8:007:274245688%259660.9
23Sign-off0:000:20000%28137#N/A
24Lunch0:300:3072323%55981
25Personal0:000:04000%119500
26Idlew/oReason0:000:09000%26908#N/A
27==============================================================4372#N/A
28Total8:308:304317985%25860#N/A
29242000
Sheet1


If the employe above is missing the "lunch" line item...the forula keeps looking until it finds "lunch" further down the sheet.
Here is the entire formula from column "L" - I then do a simple lookup from another sheet to get the scores per person.

=INDEX($H$1:$H$3000,MIN(IF($A$1:$A$3000="lunch",IF(ROW($H$1:$H$3000)-ROW($H$1)+1>MATCH($N18,$A$1:$A$3000,0),ROW($H$1:$H$3000)-ROW($H$1)+1,""))))
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sorry...I copied that formula, but forgot to change it to a "K" for the column reference.

I expect to see the #N/A references....so no surprise there.

In the example given the "L" column returns 23%...the correct reading from the selection when it comes across the "lunch" line. My problem is...that some entries don't have a "lunch" line item and the formula picks up the next instance...sometimes two or three people down the list.

I did however solve my own problem with some info. that is not shown here....I referenced another piece of data that, if it exists, drives a "zero" to be returned on the "L" column...I just made it an "if" statement based on that data.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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