VLOOKUP Function

**glennjrobertson1980** · 12-29-2014, 11:22 AM

Hi,

Please HELP!

I have 4 coloums of data. In Coloumn A, I have Road Names (i.e Jones Street, Bond Street, James Street....etc) Coloumn B contains issues (i.e. Cracked paving stone, Pothole, Ditch...etc). This information goes down the spreadsheet and there may be more than one row for each road if that road has more than one issue. (please see example below).

COLOUMN A COLOUMN B
Jones Street Cracked Paving
Jones Street Pot Hole
Bond Street Pot Hole
James Street Cracked Paving
James Street Pot Hole
James Street Ditch

I have a cell D1 that is a drop down box with a list of all the road names. I want it so when for example James Street is picked below in the nest three rows it displayes the issues with James Street. I have tried a VLOOKUP but only displays one row.

Please help, thanks

**ChemistB** · 12-29-2014, 12:03 PM

Since you are using 2003, try this array formula, copied down

=IF(COUNTIF($A$1:$A$6,$D$1)>=ROWS($A$1:$A1),INDEX($B$1:$B$6, SMALL(IF($A$1:$A$6=$D$1, ROW($A$1:$A$6)),ROWS($A$1:$A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Does that work for you?

**mrice** · 12-29-2014, 12:14 PM

Please see the attachment.

I've created a user defined function which returns the nth occurence.

=GetIssue($A$2:$A$7,$D$1,2)

=GetIssue(Range of street names,location of the drop down,occurence number)

The code is in Module1 (alt F11).