Look Up Values in row Table and return - subsequent row values

**timharding** · 04-30-2013, 10:16 AM

Look Up Values in row Table and return - subsequent row values

See File attached.

Wonder if you can help with a problem I am having.

Have tried to use LooKUp and Index but to no avail in standard format.

What I would like is
Look Up Value in B1 in data table B2:C5, and where there are multiple entry rows for the value in B1 return them as below with the corresponding data in Column C i.e. the date

United Kingdom 24/12/13
United Kingdom 25/12/13

Many thanks

Tim

**Fotis1991** · 04-30-2013, 10:24 AM

TIM. Try this.

In B12 and copy down and across this ARRAY(SEE MY SIGNATURE) FORMULA.

=IFERROR(INDEX(B$2:B$5,SMALL(IF($B$2:$B$5=$B$1,ROW(B$2:B$5)-1),ROW(B1))),"")

**benishiryo** · 04-30-2013, 10:24 AM

hi Tim, try this array formula in C12:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. 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.

**timharding** · 04-30-2013, 10:38 AM

Perfect Ben and Fottis,

Now if the data was in another Tab, How would that be referenced.

Example

The raw data is in

Tab entitled "Sheet1"

and I was the data to return in Sheet 2

Attached Example

Thanks Again

Tim

**Fotis1991** · 04-30-2013, 10:45 AM

Same way, Tim.

=IFERROR(INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$B$2:$B$5=Sheet1!$B$1,ROW(Sheet1!B$2:B$5)-1),ROW(Sheet1!B1))),"")

Copy down and across anywhere you like in Sheet2.