Another LookUp Question

Beyond Me

Board Regular
Joined
Dec 29, 2004
Messages
62
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,141,759
Messages
5,708,361
Members
421,566
Latest member
7Nabisco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top