Index & match vs vlookup

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

Tom you have been a lot of help. I have been checking out
the functions command - Index and Match vs. VLookup.

I am beginning to understand you answer. Because of that I
think I can give a more clear explanation of what I want to
do. Need to get this finish today. Here is the what I am
trying to do - forget previous posting.

1. Have 3 Workbooks.

2. WkBk 1 & 2 have a common field called S.O.#. Need to
match the exact S.O.# in WkBk 1 & 2. Then have the data on
the same row ( about 3-4 columns) import into WKBK one, but
on the same row as the matching S.O.#. This will then
create a field called P.O. # in wkbk1.


3. Now I need the P.O. # field to match the exact P.O. #
and import or return the value (text) on the same row as
the matching P.O. # in wkbk1. The result will be that the
that the S.O.# and P.O.# have the related data on 1 row.

4. Now this report will possible extracted daily, weekly
and will be overwritten.


5. The process is a report is ran in crystal to extract
data from an accounting program using Betrieve database.

6. When this report is ran it will be exported into Excel.
I want to write a macro to enter the formula every time.


7. The spreadsheet names will remain the same.

If not these command then what?

Thanks very very much in advance
 
Hi Mary
see below
2. WkBk 1 & 2 have a common field called S.O.#. Need to
match the exact S.O.# in WkBk 1 & 2. Then have the data on
the same row ( about 3-4 columns) import into WKBK one, but
on the same row as the matching S.O.#. This will then
create a field called P.O. # in wkbk1.
Click to expand...
If the S.O# is stored in column A for both workbooks enter the
following in WBK1 where the P.O # should appear:
=INDEX('wbk2'!$B$10000,MATCH(A1,'wbk2'!$A$1000,0),1)
where A1 is your lookup criteria in wbk1 and column B stores the P.O#
on wbk2
another way would be:
=VLOOKUP(A1,'wbk2'!$A$1:$B$1000,2,0)
3. Now I need the P.O. # field to match the exact P.O. #
and import or return the value (text) on the same row as
the matching P.O. # in wkbk1. The result will be that the
that the S.O.# and P.O.# have the related data on 1 row.
Click to expand...
??? I don't understand this?


Frank
 
Hey Frank the vlookup is working, however it pops up to
update wkb2. Then I have to drill down to the realy wkbk2
name which is po.xls. I tried to substitue the wkbk2 name
in the formula, but then I na. So should the real name of
the book appear in the formula? I assume I just type po in
place of wkbk2.? Almost there!!!

Mary
 
At first, I thought it was because I didn't have the file
open but it does it no matter what. When I bring up
workbook 1 - it wants to update links. I am researching
thir right now. I was able to save the correct name for
workbook2 but it still makes me drill down to the drive
letter and to the directory path for wkb2 even though it
has the correct name \seexcelpo\.

Mary
 
Hi Mary
soory that was my mistake. I thought you talked about two worksheets
not workbooks. So try the following VLOOKUP (assumption: wbk2 is
opened):
=VLOOKUP(A1,'[wbk2.xls]sheetname'!$A$1:$B$1000,2,0)

Frank
 
Hey got all the quirks worked out but one. I am using
the formula =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$B$926,2,0)
in column f2 - works fine. I am using the formula
=VLOOKUP(A2,[po.xls]Sheet1!$A$1:$C$926,3,0)in column g2
works fine. But when I tried to do the same with H2 it
doesnt work. I copy the formula into g2. I can see that
it is working. I get #NA, but when I edit the statement
to be =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$d$926,4,0)it no
longer recognizes the statement as a formula.

-----Original Message-----
Hi Mary
soory that was my mistake. I thought you talked about two worksheets
not workbooks. So try the following VLOOKUP (assumption: wbk2 is
opened):
=VLOOKUP(A1,'[wbk2.xls]sheetname'!$A$1:$B$1000,2,0)

Frank

Hey Frank the vlookup is working, however it pops up to
update wkb2. Then I have to drill down to the realy wkbk2
name which is po.xls. I tried to substitue the wkbk2 name
in the formula, but then I na. So should the real name of
the book appear in the formula? I assume I just type po in
place of wkbk2.? Almost there!!!

Mary
Click to expand...


.
Click to expand...
 
Hey got all the quirks worked out but one. I am using
the formula =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$B$926,2,0)
in column f2 - works fine. I am using the formula
=VLOOKUP(A2,[po.xls]Sheet1!$A$1:$C$926,3,0)in column g2
works fine. But when I tried to do the same with H2 it
doesnt work. I copy the formula into g2. I can see that
it is working. I get #NA, but when I edit the statement
to be =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$d$926,4,0)it no
longer recognizes the statement as a formula. Also, the
real solutions to the updatig problem was an options that
I had to uncheck - asking me to update every time.

Mary

-----Original Message-----
Hi Mary
soory that was my mistake. I thought you talked about two worksheets
not workbooks. So try the following VLOOKUP (assumption: wbk2 is
opened):
=VLOOKUP(A1,'[wbk2.xls]sheetname'!$A$1:$B$1000,2,0)

Frank

Hey Frank the vlookup is working, however it pops up to
update wkb2. Then I have to drill down to the realy wkbk2
name which is po.xls. I tried to substitue the wkbk2 name
in the formula, but then I na. So should the real name of
the book appear in the formula? I assume I just type po in
place of wkbk2.? Almost there!!!

Mary
Click to expand...


.
Click to expand...
 
Hey got all the quirks worked out but one. I am using
the formula =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$B$926,2,0)
in column f2 - works fine. I am using the formula
=VLOOKUP(A2,[po.xls]Sheet1!$A$1:$C$926,3,0)in column g2
works fine. But when I tried to do the same with H2 it
doesnt work. I copy the formula into g2. I can see that
it is working. I get #NA, but when I edit the statement
to be =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$d$926,4,0)it no
longer recognizes the statement as a formula.
 
Hey got all the quirks worked out but one. I am using
the formula =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$B$926,2,0)
in column f2 - works fine. I am using the formula
=VLOOKUP(A2,[po.xls]Sheet1!$A$1:$C$926,3,0)in column g2
works fine. But when I tried to do the same with H2 it
doesnt work. I copy the formula into g2. I can see that
it is working. I get #NA, but when I edit the statement
to be =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$d$926,4,0)it no
longer recognizes the statement as a formula.
 
Hi Mary
you may check the format of cell H2. If set to 'Text' change it to
'General' and reenter the formula. You may also change your formula a
little bit to male copying a little bit easier. Put this in F2 and copy
down/right
=VLOOKUP($A2,[po.xls]Sheet1!$A$1:$D$926,COLUMN()-4,0)

Frank
 
Frank, you are a lifesaver. It was the format of the
cell. What does that last statement do. I assume since
I will end up with 6 fields from 2nd wkbk. But I am
going to have a column from a thrid wkbk. So I assume
the -4 would go to -6? But what about the 3rd wkbk. But
this is working fine it was the format. Once I have data
in all rows, then I will test the macro. But I may need
to add formatting to the columns or use the macro? Since
some fields will be dates, general, and text?

Mary
-----Original Message-----
Hi Mary
you may check the format of cell H2. If set to 'Text' change it to
'General' and reenter the formula. You may also change your formula a
little bit to male copying a little bit easier. Put this in F2 and copy
down/right
=VLOOKUP($A2,[po.xls]Sheet1!$A$1:$D$926,COLUMN()-4,0)

Frank


Hey got all the quirks worked out but one. I am using
the formula =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$B$926,2,0)
in column f2 - works fine. I am using the formula
=VLOOKUP(A2,[po.xls]Sheet1!$A$1:$C$926,3,0)in column g2
works fine. But when I tried to do the same with H2 it
doesnt work. I copy the formula into g2. I can see that
it is working. I get #NA, but when I edit the statement
to be =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$d$926,4,0)it no
longer recognizes the statement as a formula.
Click to expand...

.
Click to expand...
 
Hi Mary
the statement COLUMN()-4 just uses your current column to choose the
col_index. E.g. If you put the VLOOKUP formula in cell F1 it will
evaluate to
COLUMN(F1)-4 = 6-4 (as column F is the 6th column)
Not requiered, you can type in your col_index manually.

Regarding the macro: Not sure whcih macro you're referring to?
Frank

Frank, you are a lifesaver. It was the format of the
cell. What does that last statement do. I assume since
I will end up with 6 fields from 2nd wkbk. But I am
going to have a column from a thrid wkbk. So I assume
the -4 would go to -6? But what about the 3rd wkbk. But
this is working fine it was the format. Once I have data
in all rows, then I will test the macro. But I may need
to add formatting to the columns or use the macro? Since
some fields will be dates, general, and text?

Mary
-----Original Message-----
Hi Mary
you may check the format of cell H2. If set to 'Text' change it to
'General' and reenter the formula. You may also change your formula a
little bit to male copying a little bit easier. Put this in F2 and
copy down/right
=VLOOKUP($A2,[po.xls]Sheet1!$A$1:$D$926,COLUMN()-4,0)

Frank


Hey got all the quirks worked out but one. I am using
the formula =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$B$926,2,0)
in column f2 - works fine. I am using the formula
=VLOOKUP(A2,[po.xls]Sheet1!$A$1:$C$926,3,0)in column g2
works fine. But when I tried to do the same with H2 it
doesnt work. I copy the formula into g2. I can see that
it is working. I get #NA, but when I edit the statement
to be =VLOOKUP(A2,[po.xls]Sheet1!$A$1:$d$926,4,0)it no
longer recognizes the statement as a formula.
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top