match and offset

[Jaybird]

I've read some tantalizing clues, but haven't yet found a solution
that works... I'm trying to use match and offset to autofill cells
with information on one worksheet from another. What I have is
worksheet one with a large list of information, several columns worth
and many, many rows. The unique identifier is in, say column Q. By
scanning a barcode onto a cell in worksheet 2 I can determine which
row of column Q contains that particular unique barcode using the
match function. What I'd like to do is to autofill the subsequent
cells in worksheet 2 with the information on that particular row of
worksheet1 using some combination of match and offset, but I can't
seem to get the syntax right. Does anyone have a clue?

 

[aidan.heritage]

I've read some tantalizing clues, but haven't yet found a solution
that works...  I'm trying to use match and offset to autofill cells
with information on one worksheet from another.  What I have is
worksheet one with a large list of information, several columns worth
and many, many rows.  The unique identifier is in, say column Q.  By
scanning a barcode onto a cell in worksheet 2 I can determine which
row of column Q contains that particular unique barcode using the
match function.  What I'd like to do is to autofill the subsequent
cells in worksheet 2 with the information on that particular row of
worksheet1 using some combination of match and offset, but I can't
seem to get the syntax right.  Does anyone have a clue?

I think you would be well advised to use the INDEX function - this
should give you what you need

 

[Jim Rech]

Let's say your table starts at Q1 and you have a Match formula in A1 that
returns the offset from Q1 that the current item matches. Say that item is
in Q10 so the Match formula returns 10. If you wanted the item next door in
R10 you could use this formula:

=OFFSET(Q1,A1-1,1)

--
Jim
| I've read some tantalizing clues, but haven't yet found a solution
| that works... I'm trying to use match and offset to autofill cells
| with information on one worksheet from another. What I have is
| worksheet one with a large list of information, several columns worth
| and many, many rows. The unique identifier is in, say column Q. By
| scanning a barcode onto a cell in worksheet 2 I can determine which
| row of column Q contains that particular unique barcode using the
| match function. What I'd like to do is to autofill the subsequent
| cells in worksheet 2 with the information on that particular row of
| worksheet1 using some combination of match and offset, but I can't
| seem to get the syntax right. Does anyone have a clue?

 

[Jaybird]

Thanks, everybody! I'm sure this will help... Just a few questions,
please. Regarding the OFFSET function... The problem I'm having is
that I can't seem to make A1 in your example equal to the result of my
MATCHCH function. In other words, I run into syntax problems when I
try to embed a MATCH. function in the OFFSET function. The resulting
equation in the example would be something like:

=OFFSET(Q1,A(MATCH(A1,Sheet1!C:C,O)-1,1)

but I can't get this to work.

As far as the INDEX function goes, I will experiment.

Thanks again!

 

[Jim Rech]

You should put the Match in its own cell if you are going to use it more
than one time., as it sounds you are. Why make Excel do the same Match many
times? Better to do it once and reference that.

Get rid of the beginning "A".

--
Jim
| Thanks, everybody! I'm sure this will help... Just a few questions,
| please. Regarding the OFFSET function... The problem I'm having is
| that I can't seem to make A1 in your example equal to the result of my
| MATCHCH function. In other words, I run into syntax problems when I
| try to embed a MATCH. function in the OFFSET function. The resulting
| equation in the example would be something like:
|
| =OFFSET(Q1,A(MATCH(A1,Sheet1!C:C,O)-1,1)
|
| but I can't get this to work.
|
| As far as the INDEX function goes, I will experiment.
|
| Thanks again!

 

[Jaybird]

Thank, Jim. But unless I misunderstand your point, this method would
only give me the row in column C of Sheet1 that has the contents of A1
of the current worksheet in it. What I need is a way to reference the
relevant cells in the same row. That is, the cells of row X that is
the result of the match function. Am I making a false assumption?

 

[Jim Rech]

Well, maybe I misunderstood you. My example returned the value in R10
(assuming the Match in A1 returned 10):

=OFFSET(Q1,A1-1,1) = R10

and therefore:

=OFFSET(Q1,A1-1,2) = value in S10
=OFFSET(Q1,A1-1,3) = value in T10
etc.

If that's not what you want then I misunderstood you, sorry. If it is then
try a few examples for yourself to get a feel for it.


--
Jim
| Thank, Jim. But unless I misunderstand your point, this method would
| only give me the row in column C of Sheet1 that has the contents of A1
| of the current worksheet in it. What I need is a way to reference the
| relevant cells in the same row. That is, the cells of row X that is
| the result of the match function. Am I making a false assumption?

 

[Jaybird]

I can see that OFFSET is not going to work. It doesn't do what I
thought it did. Makes sense. If I understand it correctly, INDEX
might be what I'm looking for. However, I can't see how it will work
without having the result of my MATCH function embedded within it. To
recap, I'm trying to figure out which row of sheet1 contains the
contents of A1 of the current sheet. (This is the result of my MATCH
formula) Then, I need to be able to reference the rest of the cells
in that row on the current worksheet. This is the problem I'm having.

 

[Jim Rech]

I can see that OFFSET is not going to work.

Well it looks like it will work to me. I must say that it's not unusual for
a newbie to visually inspect a solution he got here and conclude it won't
work. Begging them to actual _try_ it often doesn't work. Astonishing it's
it?<g>

Btw, Index will work too but I prefer Offset because it activates/references
fewer cells.

--
Jim
|I can see that OFFSET is not going to work. It doesn't do what I
| thought it did. Makes sense. If I understand it correctly, INDEX
| might be what I'm looking for. However, I can't see how it will work
| without having the result of my MATCH function embedded within it. To
| recap, I'm trying to figure out which row of sheet1 contains the
| contents of A1 of the current sheet. (This is the result of my MATCH
| formula) Then, I need to be able to reference the rest of the cells
| in that row on the current worksheet. This is the problem I'm having.
|

 

[Jaybird]

Yep. It works. Thanks very much! I'd tried to drag the formula
over, but it doesn't want to without losing the reference. I'm going
to just have to do it one cell at a time.