IF, Match, Index ? which One do I use

[Barbara]

I have a workbook with two worksheets.
One one sheet a part number will be scaned and entered into col A.
The other worksheet will have a existing list, in col A the part numbers and
col B thru F will have other information that goes with the part number.

When the user enters the part number ( using a scanner) it will appear in
col A of the first worksheet, I want the other information ( in worksheet 2 )
matching that part number to appear in col B thru F in worksheet 1.

How do I make col A search for the row with the same part number in
worksheet 2 and bring the data over to worksheet 1 in col B thru F?
Thanks,
Barbara

[Morrigan]

I'd probably use VLOOKUP() instead.

I have a workbook with two worksheets.
One one sheet a part number will be scaned and entered into col A.
The other worksheet will have a existing list, in col A the part numbers and
col B thru F will have other information that goes with the part number.

When the user enters the part number ( using a scanner) it will appear in
col A of the first worksheet, I want the other information ( in worksheet 2 )
matching that part number to appear in col B thru F in worksheet 1.

How do I make col A search for the row with the same part number in
worksheet 2 and bring the data over to worksheet 1 in col B thru F?
Thanks,
Barbara

[Bob Phillips]

B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)

copy across and down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barbara" <[email protected]> wrote in message
news:[email protected]...
> I have a workbook with two worksheets.
> One one sheet a part number will be scaned and entered into col A.
> The other worksheet will have a existing list, in col A the part numbers
and
> col B thru F will have other information that goes with the part number.
>
> When the user enters the part number ( using a scanner) it will appear in
> col A of the first worksheet, I want the other information ( in worksheet
2 )
> matching that part number to appear in col B thru F in worksheet 1.
>
> How do I make col A search for the row with the same part number in
> worksheet 2 and bring the data over to worksheet 1 in col B thru F?
> Thanks,
> Barbara

[Barbara]

It's working!
One more question, How do I copy without having to go in and change the
first cell referance back to A1 in each cells formula?
Barbara

"Bob Phillips" wrote:

> B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)
>
> copy across and down
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Barbara" <[email protected]> wrote in message
> news:[email protected]...
> > I have a workbook with two worksheets.
> > One one sheet a part number will be scaned and entered into col A.
> > The other worksheet will have a existing list, in col A the part numbers
> and
> > col B thru F will have other information that goes with the part number.
> >
> > When the user enters the part number ( using a scanner) it will appear in
> > col A of the first worksheet, I want the other information ( in worksheet
> 2 )
> > matching that part number to appear in col B thru F in worksheet 1.
> >
> > How do I make col A search for the row with the same part number in
> > worksheet 2 and bring the data over to worksheet 1 in col B thru F?
> > Thanks,
> > Barbara
>
>
>

[Bernie Deitrick]

Barbara,

=VLOOKUP($A1,Sheet2!$A$1:$F$100,Column(),False)
or
=VLOOKUP($A$1,Sheet2!$A$1:$F$100,Column(),False)

HTH,
Bernie
MS Excel MVP


"Barbara" <[email protected]> wrote in message
news:[email protected]...
> It's working!
> One more question, How do I copy without having to go in and change the
> first cell referance back to A1 in each cells formula?
> Barbara
>

[Barbara]

Yes the VLookup is working nicely. Thanks
One question though, I understand what everything is doing in the formula
except Column()
Is that giving it a variable for whatever is in the column to copy over?
Barbara
>

[Roger Govier]

Hi Barbara
Column() returns the column number A=1 B=2 etc.
In your formula, the use of COLUMN() is automatically stepping up the offset
argument in the Vlookup expression to choose the appropriate value from the
reference table.

--
Regards
Roger Govier
"Barbara" <[email protected]> wrote in message
news:[email protected]...
> Yes the VLookup is working nicely. Thanks
> One question though, I understand what everything is doing in the formula
> except Column()
> Is that giving it a variable for whatever is in the column to copy over?
> Barbara
>>

[Barbara]

Ok, what if I change a couple of things.
I need to add in four columns (a new A thru D) on Sheet 1
And the info I am now getting on Sheet 2 is in A2 through I13

So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
And it is reading from the data entered into column E in sheet 1 (E in sheet
one searchs column A in sheet 2 to get the data and bring back to sheet 1 and
put it in columns F thru L )

Since Column reads A=1, B=2 and so on, is there a way to change it to be
column F thru L.

I hope I am making since.
Barbara
"Roger Govier" wrote:

> Hi Barbara
> Column() returns the column number A=1 B=2 etc.
> In your formula, the use of COLUMN() is automatically stepping up the offset
> argument in the Vlookup expression to choose the appropriate value from the
> reference table.
>
> --
> Regards
> Roger Govier
> "Barbara" <[email protected]> wrote in message
> news:[email protected]...
> > Yes the VLookup is working nicely. Thanks
> > One question though, I understand what everything is doing in the formula
> > except Column()
> > Is that giving it a variable for whatever is in the column to copy over?
> > Barbara
> >>
>
>
>

[Roger Govier]

Hi Barbara

In any formula you can either add to or subtract from column number so
COLUMN()+4 where column =2 would refer to an offset of 6 from the value
found in VLOOKUP, rather than an offset of 2.

The originla formula given to you by Bob to place in Sheet 1 was
B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)

If you have inserted 4 columns on Sheet 1 then the formula will now be in F1
and would read
=VLOOKUP(E1,Sheet2!$A$1:$F$100,Column(),False)
Because nothing has altered on Sheet2, then it will be reading values 4
columns further over on Sheet2 than it should, so in your case we need to
subtract from COLUMN().

If I have understood you correctly, then I believe the formula should now be
F1: =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column()-4,False)

--
Regards
Roger Govier
"Barbara" <[email protected]> wrote in message
news:[email protected]...
> Ok, what if I change a couple of things.
> I need to add in four columns (a new A thru D) on Sheet 1
> And the info I am now getting on Sheet 2 is in A2 through I13
>
> So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
> And it is reading from the data entered into column E in sheet 1 (E in
> sheet
> one searchs column A in sheet 2 to get the data and bring back to sheet 1
> and
> put it in columns F thru L )
>
> Since Column reads A=1, B=2 and so on, is there a way to change it to be
> column F thru L.
>
> I hope I am making since.
> Barbara
> "Roger Govier" wrote:
>
>> Hi Barbara
>> Column() returns the column number A=1 B=2 etc.
>> In your formula, the use of COLUMN() is automatically stepping up the
>> offset
>> argument in the Vlookup expression to choose the appropriate value from
>> the
>> reference table.
>>
>> --
>> Regards
>> Roger Govier
>> "Barbara" <[email protected]> wrote in message
>> news:[email protected]...
>> > Yes the VLookup is working nicely. Thanks
>> > One question though, I understand what everything is doing in the
>> > formula
>> > except Column()
>> > Is that giving it a variable for whatever is in the column to copy
>> > over?
>> > Barbara
>> >>
>>
>>
>>

[Bob Phillips]

I think that you want

=VLOOKUP(E1,Sheet2!$E$2:$J$13,COLUMN(Sheet2!F2)-COLUMN(Sheet2!$F$2)+2,FALSE)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> Hi Barbara
>
> In any formula you can either add to or subtract from column number so
> COLUMN()+4 where column =2 would refer to an offset of 6 from the value
> found in VLOOKUP, rather than an offset of 2.
>
> The originla formula given to you by Bob to place in Sheet 1 was
> B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)
>
> If you have inserted 4 columns on Sheet 1 then the formula will now be in
F1
> and would read
> =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column(),False)
> Because nothing has altered on Sheet2, then it will be reading values 4
> columns further over on Sheet2 than it should, so in your case we need to
> subtract from COLUMN().
>
> If I have understood you correctly, then I believe the formula should now
be
> F1: =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column()-4,False)
>
> --
> Regards
> Roger Govier
> "Barbara" <[email protected]> wrote in message
> news:[email protected]...
> > Ok, what if I change a couple of things.
> > I need to add in four columns (a new A thru D) on Sheet 1
> > And the info I am now getting on Sheet 2 is in A2 through I13
> >
> > So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
> > And it is reading from the data entered into column E in sheet 1 (E in
> > sheet
> > one searchs column A in sheet 2 to get the data and bring back to sheet
1
> > and
> > put it in columns F thru L )
> >
> > Since Column reads A=1, B=2 and so on, is there a way to change it to be
> > column F thru L.
> >
> > I hope I am making since.
> > Barbara
> > "Roger Govier" wrote:
> >
> >> Hi Barbara
> >> Column() returns the column number A=1 B=2 etc.
> >> In your formula, the use of COLUMN() is automatically stepping up the
> >> offset
> >> argument in the Vlookup expression to choose the appropriate value from
> >> the
> >> reference table.
> >>
> >> --
> >> Regards
> >> Roger Govier
> >> "Barbara" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Yes the VLookup is working nicely. Thanks
> >> > One question though, I understand what everything is doing in the
> >> > formula
> >> > except Column()
> >> > Is that giving it a variable for whatever is in the column to copy
> >> > over?
> >> > Barbara
> >> >>
> >>
> >>
> >>
>
>

[Roger Govier]

Hi Bob

Barbara had said she inserted 4 columns on Sheet1.
I assumed the original table on Sheet2 had remained unaltered.
One of us might be right <bg>

--
Regards
Roger Govier
"Bob Phillips" <[email protected]> wrote in message
news:e8lA%[email protected]...
>I think that you want
>
> =VLOOKUP(E1,Sheet2!$E$2:$J$13,COLUMN(Sheet2!F2)-COLUMN(Sheet2!$F$2)+2,FALSE)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Roger Govier" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Barbara
>>
>> In any formula you can either add to or subtract from column number so
>> COLUMN()+4 where column =2 would refer to an offset of 6 from the value
>> found in VLOOKUP, rather than an offset of 2.
>>
>> The originla formula given to you by Bob to place in Sheet 1 was
>> B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)
>>
>> If you have inserted 4 columns on Sheet 1 then the formula will now be in
> F1
>> and would read
>> =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column(),False)
>> Because nothing has altered on Sheet2, then it will be reading values 4
>> columns further over on Sheet2 than it should, so in your case we need to
>> subtract from COLUMN().
>>
>> If I have understood you correctly, then I believe the formula should now
> be
>> F1: =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column()-4,False)
>>
>> --
>> Regards
>> Roger Govier
>> "Barbara" <[email protected]> wrote in message
>> news:[email protected]...
>> > Ok, what if I change a couple of things.
>> > I need to add in four columns (a new A thru D) on Sheet 1
>> > And the info I am now getting on Sheet 2 is in A2 through I13
>> >
>> > So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
>> > And it is reading from the data entered into column E in sheet 1 (E in
>> > sheet
>> > one searchs column A in sheet 2 to get the data and bring back to sheet
> 1
>> > and
>> > put it in columns F thru L )
>> >
>> > Since Column reads A=1, B=2 and so on, is there a way to change it to
>> > be
>> > column F thru L.
>> >
>> > I hope I am making since.
>> > Barbara
>> > "Roger Govier" wrote:
>> >
>> >> Hi Barbara
>> >> Column() returns the column number A=1 B=2 etc.
>> >> In your formula, the use of COLUMN() is automatically stepping up the
>> >> offset
>> >> argument in the Vlookup expression to choose the appropriate value
>> >> from
>> >> the
>> >> reference table.
>> >>
>> >> --
>> >> Regards
>> >> Roger Govier
>> >> "Barbara" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Yes the VLookup is working nicely. Thanks
>> >> > One question though, I understand what everything is doing in the
>> >> > formula
>> >> > except Column()
>> >> > Is that giving it a variable for whatever is in the column to copy
>> >> > over?
>> >> > Barbara
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>
>

[Barbara]

Thanks I will give a try.
Now I Have another issue with the same workbook.
Sheet 2 is going to be updated and sent to another company. the other
company is the one scaning the part number into sheet 1.
We will be updating Sheet 2 Twice a week.
the other company will update by coping sheet 2 into thier workbook. (sheet
2 = a souce sheet)
So when I update sheet 2 and send to them and the same part number is
entered again, if is was updated, they lose all historical data.

When the other company updates sheet 1 twice a week, how can I automate
their data to go into an ongoing spreadsheet for historical data?
Barb

"Bob Phillips" wrote:

> I think that you want
>
> =VLOOKUP(E1,Sheet2!$E$2:$J$13,COLUMN(Sheet2!F2)-COLUMN(Sheet2!$F$2)+2,FALSE)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Roger Govier" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Barbara
> >
> > In any formula you can either add to or subtract from column number so
> > COLUMN()+4 where column =2 would refer to an offset of 6 from the value
> > found in VLOOKUP, rather than an offset of 2.
> >
> > The originla formula given to you by Bob to place in Sheet 1 was
> > B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)
> >
> > If you have inserted 4 columns on Sheet 1 then the formula will now be in
> F1
> > and would read
> > =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column(),False)
> > Because nothing has altered on Sheet2, then it will be reading values 4
> > columns further over on Sheet2 than it should, so in your case we need to
> > subtract from COLUMN().
> >
> > If I have understood you correctly, then I believe the formula should now
> be
> > F1: =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column()-4,False)
> >
> > --
> > Regards
> > Roger Govier
> > "Barbara" <[email protected]> wrote in message
> > news:[email protected]...
> > > Ok, what if I change a couple of things.
> > > I need to add in four columns (a new A thru D) on Sheet 1
> > > And the info I am now getting on Sheet 2 is in A2 through I13
> > >
> > > So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
> > > And it is reading from the data entered into column E in sheet 1 (E in
> > > sheet
> > > one searchs column A in sheet 2 to get the data and bring back to sheet
> 1
> > > and
> > > put it in columns F thru L )
> > >
> > > Since Column reads A=1, B=2 and so on, is there a way to change it to be
> > > column F thru L.
> > >
> > > I hope I am making since.
> > > Barbara
> > > "Roger Govier" wrote:
> > >
> > >> Hi Barbara
> > >> Column() returns the column number A=1 B=2 etc.
> > >> In your formula, the use of COLUMN() is automatically stepping up the
> > >> offset
> > >> argument in the Vlookup expression to choose the appropriate value from
> > >> the
> > >> reference table.
> > >>
> > >> --
> > >> Regards
> > >> Roger Govier
> > >> "Barbara" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > Yes the VLookup is working nicely. Thanks
> > >> > One question though, I understand what everything is doing in the
> > >> > formula
> > >> > except Column()
> > >> > Is that giving it a variable for whatever is in the column to copy
> > >> > over?
> > >> > Barbara
> > >> >>
> > >>
> > >>
> > >>
> >
> >
>
>
>

[Barbara]

I used the formula Roger wrote, I tried Bob's and couldnt get it to work, but
Rodgers did. I understood it more too.
But I thank you both very, very much, you have both been helpful.
Barbara

"Roger Govier" wrote:

> Hi Bob
>
> Barbara had said she inserted 4 columns on Sheet1.
> I assumed the original table on Sheet2 had remained unaltered.
> One of us might be right <bg>
>
> --
> Regards
> Roger Govier
> "Bob Phillips" <[email protected]> wrote in message
> news:e8lA%[email protected]...
> >I think that you want
> >
> > =VLOOKUP(E1,Sheet2!$E$2:$J$13,COLUMN(Sheet2!F2)-COLUMN(Sheet2!$F$2)+2,FALSE)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Roger Govier" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi Barbara
> >>
> >> In any formula you can either add to or subtract from column number so
> >> COLUMN()+4 where column =2 would refer to an offset of 6 from the value
> >> found in VLOOKUP, rather than an offset of 2.
> >>
> >> The originla formula given to you by Bob to place in Sheet 1 was
> >> B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)
> >>
> >> If you have inserted 4 columns on Sheet 1 then the formula will now be in
> > F1
> >> and would read
> >> =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column(),False)
> >> Because nothing has altered on Sheet2, then it will be reading values 4
> >> columns further over on Sheet2 than it should, so in your case we need to
> >> subtract from COLUMN().
> >>
> >> If I have understood you correctly, then I believe the formula should now
> > be
> >> F1: =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column()-4,False)
> >>
> >> --
> >> Regards
> >> Roger Govier
> >> "Barbara" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Ok, what if I change a couple of things.
> >> > I need to add in four columns (a new A thru D) on Sheet 1
> >> > And the info I am now getting on Sheet 2 is in A2 through I13
> >> >
> >> > So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
> >> > And it is reading from the data entered into column E in sheet 1 (E in
> >> > sheet
> >> > one searchs column A in sheet 2 to get the data and bring back to sheet
> > 1
> >> > and
> >> > put it in columns F thru L )
> >> >
> >> > Since Column reads A=1, B=2 and so on, is there a way to change it to
> >> > be
> >> > column F thru L.
> >> >
> >> > I hope I am making since.
> >> > Barbara
> >> > "Roger Govier" wrote:
> >> >
> >> >> Hi Barbara
> >> >> Column() returns the column number A=1 B=2 etc.
> >> >> In your formula, the use of COLUMN() is automatically stepping up the
> >> >> offset
> >> >> argument in the Vlookup expression to choose the appropriate value
> >> >> from
> >> >> the
> >> >> reference table.
> >> >>
> >> >> --
> >> >> Regards
> >> >> Roger Govier
> >> >> "Barbara" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Yes the VLookup is working nicely. Thanks
> >> >> > One question though, I understand what everything is doing in the
> >> >> > formula
> >> >> > except Column()
> >> >> > Is that giving it a variable for whatever is in the column to copy
> >> >> > over?
> >> >> > Barbara
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>
>
>

[Roger Govier]

Hi Barbara

One way would be to have another file called Archive.
Before updating Sheet2, right click on Sheet2 tab, choose Move or Copy,
Select Copy and in the white pane To Book enter Archive.
In the Archive file, right click on the sheet tab and Rename to the Date the
copy was made.
If you think the file is getting too large, have ArchiveQ1, ArchiveQ2 etc.

--
Regards
Roger Govier
"Barbara" <[email protected]> wrote in message
news:[email protected]...
> Thanks I will give a try.
> Now I Have another issue with the same workbook.
> Sheet 2 is going to be updated and sent to another company. the other
> company is the one scaning the part number into sheet 1.
> We will be updating Sheet 2 Twice a week.
> the other company will update by coping sheet 2 into thier workbook.
> (sheet
> 2 = a souce sheet)
> So when I update sheet 2 and send to them and the same part number is
> entered again, if is was updated, they lose all historical data.
>
> When the other company updates sheet 1 twice a week, how can I automate
> their data to go into an ongoing spreadsheet for historical data?
> Barb
>
> "Bob Phillips" wrote:
>
>> I think that you want
>>
>> =VLOOKUP(E1,Sheet2!$E$2:$J$13,COLUMN(Sheet2!F2)-COLUMN(Sheet2!$F$2)+2,FALSE)
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "Roger Govier" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Barbara
>> >
>> > In any formula you can either add to or subtract from column number so
>> > COLUMN()+4 where column =2 would refer to an offset of 6 from the value
>> > found in VLOOKUP, rather than an offset of 2.
>> >
>> > The originla formula given to you by Bob to place in Sheet 1 was
>> > B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)
>> >
>> > If you have inserted 4 columns on Sheet 1 then the formula will now be
>> > in
>> F1
>> > and would read
>> > =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column(),False)
>> > Because nothing has altered on Sheet2, then it will be reading values 4
>> > columns further over on Sheet2 than it should, so in your case we need
>> > to
>> > subtract from COLUMN().
>> >
>> > If I have understood you correctly, then I believe the formula should
>> > now
>> be
>> > F1: =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column()-4,False)
>> >
>> > --
>> > Regards
>> > Roger Govier
>> > "Barbara" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > Ok, what if I change a couple of things.
>> > > I need to add in four columns (a new A thru D) on Sheet 1
>> > > And the info I am now getting on Sheet 2 is in A2 through I13
>> > >
>> > > So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
>> > > And it is reading from the data entered into column E in sheet 1 (E
>> > > in
>> > > sheet
>> > > one searchs column A in sheet 2 to get the data and bring back to
>> > > sheet
>> 1
>> > > and
>> > > put it in columns F thru L )
>> > >
>> > > Since Column reads A=1, B=2 and so on, is there a way to change it to
>> > > be
>> > > column F thru L.
>> > >
>> > > I hope I am making since.
>> > > Barbara
>> > > "Roger Govier" wrote:
>> > >
>> > >> Hi Barbara
>> > >> Column() returns the column number A=1 B=2 etc.
>> > >> In your formula, the use of COLUMN() is automatically stepping up
>> > >> the
>> > >> offset
>> > >> argument in the Vlookup expression to choose the appropriate value
>> > >> from
>> > >> the
>> > >> reference table.
>> > >>
>> > >> --
>> > >> Regards
>> > >> Roger Govier
>> > >> "Barbara" <[email protected]> wrote in message
>> > >> news:[email protected]...
>> > >> > Yes the VLookup is working nicely. Thanks
>> > >> > One question though, I understand what everything is doing in the
>> > >> > formula
>> > >> > except Column()
>> > >> > Is that giving it a variable for whatever is in the column to copy
>> > >> > over?
>> > >> > Barbara
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>>
>>
>>