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

#### jcarroll01

##### Board Regular
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
19ScheduledScheduledActualMin.InMin.OutPerc.In247311
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### barry houdini

##### MrExcel MVP
Are you saying you shouldn't get the #N/A errors?

What's in column N?

#### jcarroll01

##### Board Regular
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.

Replies
2
Views
707
Replies
0
Views
534
Replies
7
Views
378
Replies
3
Views
1K
Replies
2
Views
457

1,171,060
Messages
5,873,526
Members
432,982
Latest member
meepio

### 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.

### Which adblocker are you using?

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

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