Looking Up Values

**Stuie** · 10-08-2006, 08:29 AM

i have 2 sheets and on 1 i have a data list and on the other i have a results table.

data list eg:

¦ = Cells

¦ Jo ¦ 35 ¦ 120 ¦
¦ John ¦ 23 ¦ 100 ¦
¦ John ¦ 29 ¦ 100 ¦
¦ Ben ¦ 32 ¦ 120 ¦
etc...

now on the next sheet looks like this:

¦ Name ¦ No1 ¦
¦ John ¦" "¦
¦ John ¦" "¦

now under No1 on first line i want it to say 23 and on second line 29

i know i can vlookup the 23 but how do i get the 29 in the cell below??

need anymore explaination PM me

**Bryan Hessey** · 10-08-2006, 09:47 AM

Originally Posted by Stuie

i have 2 sheets and on 1 i have a data list and on the other i have a results table.

data list eg:

¦ = Cells

¦ Jo ¦ 35 ¦ 120 ¦
¦ John ¦ 23 ¦ 100 ¦
¦ John ¦ 29 ¦ 100 ¦
¦ Ben ¦ 32 ¦ 120 ¦
etc...

now on the next sheet looks like this:

¦ Name ¦ No1 ¦
¦ John ¦" "¦
¦ John ¦" "¦

now under No1 on first line i want it to say 23 and on second line 29

i know i can vlookup the 23 but how do i get the 29 in the cell below??

need anymore explaination PM me

You could defiine the table as starting on the first Match +1

=VLOOKUP(A2,INDIRECT("Sheet2!A"&2+(MATCH(A2,Sheet2!A2:A10,0))&":B10"),2,FALSE)

Amended, correctly that formula, for cell C2, using data starting in A2 and a table on Sheet2A2:should be:
=VLOOKUP(A2,INDIRECT("Sheet2!A"&2+(IF(COUNTIF(A$2:A2,A2)>1,MATCH(A2,Sheet2!A$2:A2,0),0))&":B10"),2,FALSE)

but this will not find subsequent matches
---

**starguy** · 10-09-2006, 03:13 AM

Originally Posted by Stuie

i have 2 sheets and on 1 i have a data list and on the other i have a results table.

data list eg:

¦ = Cells

¦ Jo ¦ 35 ¦ 120 ¦
¦ John ¦ 23 ¦ 100 ¦
¦ John ¦ 29 ¦ 100 ¦
¦ Ben ¦ 32 ¦ 120 ¦
etc...

now on the next sheet looks like this:

¦ Name ¦ No1 ¦
¦ John ¦" "¦
¦ John ¦" "¦

now under No1 on first line i want it to say 23 and on second line 29

i know i can vlookup the 23 but how do i get the 29 in the cell below??

need anymore explaination PM me

you need to add a helper column. if you have names in sheet1 in col A click on any cell in col A right click > Insert... > Entire column. (now names are in col B and other data in col C and D)
put in A1

=B1&COUNTIF($B$1:B1,B1) and copy it down.

in sheet2 use following VLOOKUP in cell B1 and copy it down (I supposed that you entered name in cell A1 downward)

=VLOOKUP(A1&ROW(A1),Sheet1!A1:C50,3,FALSE)

Regards.

**Stuie** · 10-09-2006, 04:22 AM

thats cool m8 cheers but the only problem is i got it to work then i did sumthin and cant get it goin again...

if i send u a sample cud u put it in so i can c what im doin wrong??

**starguy** · 10-09-2006, 05:08 AM

Originally Posted by Stuie

thats cool m8 cheers but the only problem is i got it to work then i did sumthin and cant get it goin again...

if i send u a sample cud u put it in so i can c what im doin wrong??

post here sample.