Index & Match to ignore blanks

[TheBarman]

Hi team, hoping you can help with the index,match formula to find the occurrence where the storage unit is not blank (or zero). The formula is referring to a table (called "Runners") in another sheet. The table is refreshed every hour throughout the day to bring in new data. Unfortunately there are more columns of data than the sample below, so I am unable to use a pivot table or vlookup and must be an INDEX.


Here is where I am up to with the formula:

=IF(INDEX(Runners[[#All],[Storage Unit]],MATCH(F2,Runners[[#All],[Name]],0))<>0,INDEX(Runners[[#All],[Location]],MATCH(F2,Runners[[#All],[Name]],0)),"Give me second occurance")

F2 refers to another sheet with Names


Sample of table:

[TABLE="width: 211"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[TD]Storage Unit[/TD]
[/TR]
[TR]
[TD]Michael[/TD]
[TD]Brisbane[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Sydney[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Louise[/TD]
[TD]Melbourne[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Perth[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Louise[/TD]
[TD]Adelaide[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]


Outcome:
Index Louise to find "Adelaide" because there is a value in the storage unit column [9] - not "Melbourne" because it's the first Match.
Index Peter to find "Perth" because there is a value in the storage unit column [5] - not "Sydney" because it's the first Match.



Appreciate your help.
Dave.

 

[AhoyNC]

Maybe something like this:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
If someone can have more than 1 Unit, drag the formula down as needed and it will return all locations.

Excel Workbook
	A	B	C
1	Name	Location	Storage Unit
2	Michael	Brisbane	4
3	Peter	Sydney
4	Louise	Melbourne
5	Peter	Perth	5
6	Louise	Adelaide	9
7
8
9	Name	Louise
10	Location(s)	Adelaide
11
12
Sheet

 

[Aladin Akyurek]

Also, using the Table references...

Control+shift+enter, not just enter:

=INDEX(Runners[Storage Unit],MATCH(F2,IF(ISNUMBER(Runners[Storage Unit]),Runners[Name]),0))

where F2 = Louise.

 

[TheBarman]

Brilliant. Thank you so much AhoyNC. Works like a charm.

 

[TheBarman]

Thank you Aladin Akyurek for the post. I haven't been able to test this yet, but will give a go on Monday when I'm back at work.

 

[AhoyNC]

You're welcome. Thanks for the feedback.