# Another LookUp Question

#### Beyond Me

##### Board Regular
I have many rows of data held on a sheet called DataRecords. In column 'A' which holds an identifier and appears for four rows before the next group of four appears and so on.

On another sheet called Summary I need to set up a sort of lookup, which I'm struggling with (it will form a report). In cell A1 I want to enter the identifier and in cells A4:A7 a vlookup.

In cell A4 I'm currently using =VLOOKUP(A\$1,ID,2,FALSE) which brings in the first record.

I realise that its finding the first row, how can I pick up the second row in cell A5?

This is my data:
Book1
ABCDEFGH
1ID01-Jan02-Jan03-Jan04-Jan05-Jan06-Jan07-Jan
2A0.8311940.5182650.0962670.6451990.7064290.0499460.727178
3A0.7301610.1259680.3453680.0735730.7714820.9408020.823102
4A0.9006420.4208470.3471480.5510120.6974350.7221710.648806
5A0.1303330.2085640.5145020.1336170.4679680.1293750.804516
6S0.3581890.9870240.7741420.0576360.2552440.960970.898696
7S0.3015170.4106830.5290340.8511010.8773080.2545520.164747
8S0.1891640.9947050.2122210.4366110.1664590.004980.120284
9S0.5158580.4485890.800540.3170560.0156370.7779090.050943
DataRecords

This is what I'm after:
Book1
ABCD
1A
2
3This is what I'm getting.This is what I should get
40.8311936870.831193687
50.8311936870.730161352
60.8311936870.900642452
70.8311936870.130332679
8
Summary

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Book3
ABCD
1A4
21-Jan-061
30.831194
40.730161
50.900642
60.130333
7
Summary

A1:A2 houses user entries, an ID and a date, respectively.

B1:

=COUNTIF(DataRecords!\$A\$2:\$A\$9,A1)

B2:

=MATCH(A2,DataRecords!\$B\$1:\$H\$1,1)

A3:

=IF(ROWS(\$A\$3:A3)<=\$B\$1,INDEX(DataRecords!\$B\$2:\$H\$9,SMALL(IF(DataRecords!\$A\$2:\$A\$9=\$A\$1,ROW(DataRecords!\$A\$2:\$A\$9)-ROW(DataRecords!\$A\$2)+1),ROWS(\$A\$3:A3)),\$B\$2),"")

which is confirmed with control+shift+enter (not with enter) then copied down.

Replies
3
Views
132
Replies
3
Views
256
Replies
1
Views
144
Replies
12
Views
715
Replies
10
Views
277

1,219,899
Messages
6,150,842
Members
450,987
Latest member
PopeScooby

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