How to search for a value in a column in another Excel file

M

MSSailor

I want to pick up data from another excel file "A" (worksheet) when I write a
value in Excel file (B) at specified cell, in this case a four digit number.
Let say that I search data for value "9999". In file "A" there is a row at
column A that contains "9999". Then I would like to add 3 different cell data
from same row as "9999" automatically.
In other words:
When I write "9999" in cell "B5" in file "B" search for value "9999" in
column A=9999 let us say that "9999" is in row A3 in file "A"
I want to import value "D3" to file "B" cell "B6"
I want to import value "E3" to file "B" cell "B7"
I want to import value "F3" to file "B" cell "B8"

Thanks for any ones help! The two files are in the same PC and same directory
 
S

Sheeloo

Enter this in B6 in File B (assuming Book3.xls is the name of the file A and
your data is in Sheet1)
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,4,FALSE)

this in B7
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,5,FALSE)

and this in B8
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,6,FALSE)

It will give you #N/A if the value in B5 is not found in Col A of Book3.xls
 
C

Chris Bode

Well, you can accomplish this task using VLookUp
I think you are trying to get values from one work sheet to other a
you have mentioned as file “A” (worksheet),,if so
Then,
1.select cell B6 in next worksheet, and enter following formula
=VLOOKUP(B5,Sheet1!A1:F3,2,FALSE)
2.select B7 and enter following formula
=VLOOKUP(B5,Sheet1!A1:F3,3,FALSE)
3.select cell B8 and enter following formula
=VLOOKUP(B5,Sheet1!A1:F3,4,FALSE)

If you are using different excel files then vlookup can be helpful i
similar way as well….



Chris
 
M

MSSailor

What does the value "..$F,4,FALSE)" stand for?
Cell value B5 is "9999" for instance
I get that cell B6 is vlookup the value in cell b5 in xls file named
"book3.xls" sheet1 in column A. So far so good.
Now I want the value from same row as, in this case "9999" that exist in
column "D"


Sheeloo said:
Enter this in B6 in File B (assuming Book3.xls is the name of the file A and
your data is in Sheet1)
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,4,FALSE)

this in B7
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,5,FALSE)

and this in B8
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,6,FALSE)

It will give you #N/A if the value in B5 is not found in Col A of Book3.xls

MSSailor said:
I want to pick up data from another excel file "A" (worksheet) when I write a
value in Excel file (B) at specified cell, in this case a four digit number.
Let say that I search data for value "9999". In file "A" there is a row at
column A that contains "9999". Then I would like to add 3 different cell data
from same row as "9999" automatically.
In other words:
When I write "9999" in cell "B5" in file "B" search for value "9999" in
column A=9999 let us say that "9999" is in row A3 in file "A"
I want to import value "D3" to file "B" cell "B6"
I want to import value "E3" to file "B" cell "B7"
I want to import value "F3" to file "B" cell "B8"

Thanks for any ones help! The two files are in the same PC and same directory
Click to expand...
Click to expand...
 
S

Sheeloo

=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,4,FALSE)
The above means that take the value in B5, go to Sheet1 of Book3, look down
Column A.... When you find the value matching B5 move across 4 columns (Col
D) and pick the value and return it to the cell containing the formula...
FALSE means make an exact match.... If it was TRUE then neares match would
be found but then Col A has to be sorted in ascending order...

MSSailor said:
What does the value "..$F,4,FALSE)" stand for?
Cell value B5 is "9999" for instance
I get that cell B6 is vlookup the value in cell b5 in xls file named
"book3.xls" sheet1 in column A. So far so good.
Now I want the value from same row as, in this case "9999" that exist in
column "D"


Sheeloo said:
Enter this in B6 in File B (assuming Book3.xls is the name of the file A and
your data is in Sheet1)
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,4,FALSE)

this in B7
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,5,FALSE)

and this in B8
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,6,FALSE)

It will give you #N/A if the value in B5 is not found in Col A of Book3.xls

MSSailor said:
I want to pick up data from another excel file "A" (worksheet) when I write a
value in Excel file (B) at specified cell, in this case a four digit number.
Let say that I search data for value "9999". In file "A" there is a row at
column A that contains "9999". Then I would like to add 3 different cell data
from same row as "9999" automatically.
In other words:
When I write "9999" in cell "B5" in file "B" search for value "9999" in
column A=9999 let us say that "9999" is in row A3 in file "A"
I want to import value "D3" to file "B" cell "B6"
I want to import value "E3" to file "B" cell "B7"
I want to import value "F3" to file "B" cell "B8"

Thanks for any ones help! The two files are in the same PC and same directory
Click to expand...
Click to expand...
Click to expand...
 
M

MSSailor

Great what you tell me! I get " #NAME? " in my B6. How is the function in B6
executed.
Have made the "book3.xls" and column A in ascending order.

As I understand the "book3.xls" does not need to be open?

Sheeloo said:
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,4,FALSE)
The above means that take the value in B5, go to Sheet1 of Book3, look down
Column A.... When you find the value matching B5 move across 4 columns (Col
D) and pick the value and return it to the cell containing the formula...
FALSE means make an exact match.... If it was TRUE then neares match would
be found but then Col A has to be sorted in ascending order...

MSSailor said:
What does the value "..$F,4,FALSE)" stand for?
Cell value B5 is "9999" for instance
I get that cell B6 is vlookup the value in cell b5 in xls file named
"book3.xls" sheet1 in column A. So far so good.
Now I want the value from same row as, in this case "9999" that exist in
column "D"


Sheeloo said:
Enter this in B6 in File B (assuming Book3.xls is the name of the file A and
your data is in Sheet1)
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,4,FALSE)

this in B7
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,5,FALSE)

and this in B8
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,6,FALSE)

It will give you #N/A if the value in B5 is not found in Col A of Book3.xls

:

I want to pick up data from another excel file "A" (worksheet) when I write a
value in Excel file (B) at specified cell, in this case a four digit number.
Let say that I search data for value "9999". In file "A" there is a row at
column A that contains "9999". Then I would like to add 3 different cell data
from same row as "9999" automatically.
In other words:
When I write "9999" in cell "B5" in file "B" search for value "9999" in
column A=9999 let us say that "9999" is in row A3 in file "A"
I want to import value "D3" to file "B" cell "B6"
I want to import value "E3" to file "B" cell "B7"
I want to import value "F3" to file "B" cell "B8"

Thanks for any ones help! The two files are in the same PC and same directory
Click to expand...
Click to expand...
Click to expand...
 
S

Sheeloo

With FALSE in your VLOOKUP formula, you don't need to sort...
If you are getting #N/A then value in B5 is not found in Col A of
[Book3.xls]Sheet1

Book3 need not be open.

Format may be different...

MSSailor said:
Great what you tell me! I get " #NAME? " in my B6. How is the function in B6
executed.
Have made the "book3.xls" and column A in ascending order.

As I understand the "book3.xls" does not need to be open?

Sheeloo said:
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,4,FALSE)
The above means that take the value in B5, go to Sheet1 of Book3, look down
Column A.... When you find the value matching B5 move across 4 columns (Col
D) and pick the value and return it to the cell containing the formula...
FALSE means make an exact match.... If it was TRUE then neares match would
be found but then Col A has to be sorted in ascending order...

MSSailor said:
What does the value "..$F,4,FALSE)" stand for?
Cell value B5 is "9999" for instance
I get that cell B6 is vlookup the value in cell b5 in xls file named
"book3.xls" sheet1 in column A. So far so good.
Now I want the value from same row as, in this case "9999" that exist in
column "D"


:

Enter this in B6 in File B (assuming Book3.xls is the name of the file A and
your data is in Sheet1)
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,4,FALSE)

this in B7
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,5,FALSE)

and this in B8
=VLOOKUP($B$5,[Book3.xls]Sheet1!$A:$F,6,FALSE)

It will give you #N/A if the value in B5 is not found in Col A of Book3.xls

:

I want to pick up data from another excel file "A" (worksheet) when I write a
value in Excel file (B) at specified cell, in this case a four digit number.
Let say that I search data for value "9999". In file "A" there is a row at
column A that contains "9999". Then I would like to add 3 different cell data
from same row as "9999" automatically.
In other words:
When I write "9999" in cell "B5" in file "B" search for value "9999" in
column A=9999 let us say that "9999" is in row A3 in file "A"
I want to import value "D3" to file "B" cell "B6"
I want to import value "E3" to file "B" cell "B7"
I want to import value "F3" to file "B" cell "B8"

Thanks for any ones help! The two files are in the same PC and same directory
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop down list control value in vba 2
Search cell value in same column 3
Help on Formula 5
Word macro that can call cells out of Excel for a find and replace 0
Search two columns in list for first and last name and return Id 5
Merging from an Excel file with graphics 1
Calculate the median value before a specified list 0
Autonumber field in Excel that behaves the same as Access 0

Top