Need lookup formula

[lothario]

Hi,

I have 2 spreadsheets s1 and s2:

s1 has 10,000 rows of data.

s2 has 40,000 rows of data.

The data in s1 is a subset of the data in s2.
(So s2 is the superset.)

Both s1 and s2 have columns called index and cost.

The index column has unique values in s1 and s2.
The index column in s1 is not sorted.
The index column in s2 is sorted.

The cost column does not contain unique values in s1 and s2.

The cost column in s2 contains no blanks.
The cost column in s1 contains blanks. And this is the problem. This
is incomplete data.

I need fill the blanks in the cost column in s1.
To accomplish this:

a. I need to put a formula in every cell in of the cost column in s1.
b. The formula will grab the index value in the same row of cost in
s1.
c. The formula will find the same index value in the index column of
s2.
d. The formula will then find the corresponding cost in the same row
of s2.
e. The formula will retun that cost value to the respective cost cell
in s1.

I am struggling with the various lookup functions.
I am not getting the correct cost values.
Can you please build this formula for me?

Thanks.
Luther

 

[Pete McCosh]

Assuming S1 and S2 are separate files, not just separate
sheets in the same file, your index is column A, the costs
are column B, Row 1 is titles and the the sheet in s2 is
called Data, try this in S1, cell b2:

=vlookup(a2,'C:\My Documents\[s2.xls]!
Data'$A$1:$B$40000,2,false)

Amend the file path, sheet name, range reference and the
lookup reference (in this case ",2," for column B) as
required.

Pete

 

[lothario]

Got it. Thanks Pete.