Formula(s) to match cell value

[Jotoya]

Hello!

Here's my dilemma: I have 2 sheets - "Main", "Info".

In the "Info" sheet I have a column (any column will do for this post example) whereby each row in the column has a location name that repeats - Loc1, Loc1, Loc1. Next to this location name there is subsequent data such as date and notes. Here's the catch: The data that is next to this same location will change slightly. For instance:

Location Date Notes
LOC1 6/2/16 Employee failed to enter data to scanner
LOC1 6/2/16 Manager increased throughput
LOC1 6/3/16 Tugger broke
LOC34 7/1/16 Notes
LOC48 7/12/16 More notes

In the "Main" sheet, I have a cell validated drop down which contains each location name which I will come back to in a moment.

I essentially would like on the "Main" sheet to have a formula(s), maybe MATCH or INDEX or VLOOKUP/HLOOKUP, that will only look to the location name selected in the validated cell, and display that location's name and data for ALL instances. So from my data example above, if I chose LOC1 in the drop down then the formulas would fire and obtain all instance plus its related data for LOC1 and display it in the "Main" sheet.

I hope this makes sense. Please reply back if my question has thoroughly confused you and you need extra clarification.

Thank you in advance for any assistance.

 

[svendiamond]

Is VBA an option? You can use a crazy INDEX/MATCH formula but it's so much easier using VBA. For me, at least. Aladdin or Peter or one of the other experts on this sub are really good at the INDEX/MATCH dynamic lookups but they're hard to get my head around. You could just Google "INDEX/MATCH multiple results" and find one of their posts. But if you can use VBA I can write a quick script for that. Just share a screenshot of your sheets so I know which cells go where.

 

[Jotoya]

Is VBA an option? You can use a crazy INDEX/MATCH formula but it's so much easier using VBA. For me, at least. Aladdin or Peter or one of the other experts on this sub are really good at the INDEX/MATCH dynamic lookups but they're hard to get my head around. You could just Google "INDEX/MATCH multiple results" and find one of their posts. But if you can use VBA I can write a quick script for that. Just share a screenshot of your sheets so I know which cells go where.

svendiamond,

Thank you SO MUCH for peeking at my post and offering not only to assist me but providing me with an excellent google search suggestion. Believe it or not the google suggestion you gave me worked! This link: How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP provided me with EXACTLY what I was looking for. I modified it to suit my need and it worked like a charm plus I got to learn more about array formulas. They sure are powerful but boy do they use A LOT of computational power! Thanks again svendiamond!

Regards,
Jose

 

[Aladin Akyurek]

svendiamond,

Thank you SO MUCH for peeking at my post and offering not only to assist me but providing me with an excellent google search suggestion. Believe it or not the google suggestion you gave me worked! This link: How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP provided me with EXACTLY what I was looking for. I modified it to suit my need and it worked like a charm plus I got to learn more about array formulas. They sure are powerful but boy do they use A LOT of computational power! Thanks again svendiamond!

Regards,
Jose

I disagree with what they advertise though, i.e.

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

Expensive, not robust/erroneous... Rather:

In F1 enter:

=COUNTIF($A$1:$A$8,$E$1)

In E2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$2:E2)<=$F$1,INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)-ROW($A$1)+1),ROWS($E$2:E2))),"")