VLOOKUP not quite right...

  • Thread starter Thread starter mousetrap
  • Start date Start date
M

mousetrap

I have a worksheet with job numbers in column A which correspond to
data in four rows of the other columns, ie every 4 rows in column A
there is a new job number. Interim column A cells are blank.

On another worksheet I am trying to put, in horizontal format, the
value in column A then data extracted from another column, eg

A10, G10, G11, G12
A14, G14, G15, G16
A18, G18, G19, G20...

I could use VLOOKUP to do G10, G14, G18, but that doesn't help with
those data values that are not on the same row as the job numbers.

I have been looking at INDEX and OFFSET and not wholly understanding
them, but don't think there's anything there that will help me by
itself. Wondered whether anyone knew any funky combination that might
do the job.

Other info:

I am not looking to sum any of these cells, just list the values from
the original cells in adjacent horizontal cells on the other sheet.

There isn't any unique column title on the column (G) in question that
could be used for an HLOOKUP, or any mention of the job number in
column G.

Would appreciate any ideas, thanks...
mousy

--
 
The second sheet (sheet2) is completely empty, right?

First thing, I'd do is copy column A of the first sheet (sheet1) to sheet2.

Then select column A and do:
Edit|goto|special
Check blanks and hit ok.

Then rightclick on one of those selected blank cells and right click and choose
delete. (entire row is fine as is shift up.)

Now you have column A done (woohoo!)

In B1:
=INDEX(Sheet1!$G:$G,MATCH($A1,Sheet2!$A:$A,0)+1)
In C1:
=INDEX(Sheet1!$G:$G,MATCH($A1,Sheet2!$A:$A,0)+2)
In D1:
=INDEX(Sheet1!$G:$G,MATCH($A1,Sheet2!$A:$A,0)+3)

The important part of each of those formulas is that =match() section.

It returns the row of the match. So we just add 1 (or 2 or 3) to that value.

You may want to read Debra Dalgleish explanations for both =vlookup() and
=index(match()) at:

http://www.contextures.com/xlFunctions02.html
 
In B1:
=INDEX(Sheet1!$G:$G,MATCH($A1,Sheet2!$A:$A,0)+1)
In C1:
=INDEX(Sheet1!$G:$G,MATCH($A1,Sheet2!$A:$A,0)+2)
In D1:
=INDEX(Sheet1!$G:$G,MATCH($A1,Sheet2!$A:$A,0)+3)

The important part of each of those formulas is that =match() section.

It returns the row of the match. So we just add 1 (or 2 or 3) to that value.
Click to expand...

Brill. Found it was +0, +1, +2, rather than +1, +2, +3, so just a
minor tweak.

It's now looking just how I want it to, so big thanks for your help.

mousy


--
 
Back
Top