VLOOKUP and ???

[mpquin]

Hey experts.

I have a data source that I am trying pull data from as a roll up and I am having a difficult time with a solution.

I am basically trying to pull data directly below the cell of a VLOOKUP. Please see the attached.

This is just a small example of the actual data. The solution for the gross scores is very simple using VLOOKUP. However, I need assistance with the net scores. Hoping this makes sense once you see the file.

Thanks,
Mike

[NBVC]

do you mean?

=HLOOKUP(C2,$Q$3:$Y$5,2,0)

or

=LOOKUP(C2,$Q$3:$Y$5)

copied across

[martindwilson]

yep that is simple too simple as shown you could just put =Q5;=R5 .........
so what do you want to look up from where?

[daddylonglegs]

Hello Mike,

I assume you'd be looking up based on the name with multiple names in the data. Try a formula like this in C3 copied across

=INDEX(Q5:Q101,MATCH($A3,$O4:$O100,0))

Note the difference in ranges, that's what gives you the next row

[NBVC]

Actually, if you are looking to match up with a name too... then try:

=SUMPRODUCT(($O$4:$O$10=$A3)*($Q$3:$Y$3=C2),$Q$5:$Y$11)

adjust ranges to suit... notice.. the last range is offset vertically by one start/finish.

[mpquin]

I need to compare the reference the name ("Mark"), then go across to the hole but reference the cell below.

So in the attached example, to find the "gross" score on Hole #1, the formula is vlookup(a3,O4:Z5,3,0).

I need to import the net score on the example which is in cell Q5. One cell below the result of the vlookup.

The data changes and is referenced in various cells on multiple sheets so a simple "=Q5" is not applicable.

[NBVC]

Other possible solutions were submitted too.

[mpquin]

Thanks guys! Got what I needed.

[ychartra01]

Add two columns to the left of your source data.

label them Key1 and Key2.

In the Key1 column add the following formula

Please Login or Register  to view this content.

This will fetch the player name. if the field to the right is blank it will fetch carry over the player from above. (i.e fill in the blank)

In key2 to the left of Key1 add the following formula

Please Login or Register  to view this content.

This will concatenate the player name with the type of score record (i.e. net or gross)

You can later hide the columns Key1 or Key2. You can also label them something else.

In your cell where you want the net score to appear paste the following formula

Please Login or Register  to view this content.

I do this a lot when I get pivot table data that I need to re-use elswhere. I've attached your speadsheet with my solution.

Hope this works.