How do i retreive data from another worksheet to a current one?

[dphi201]

I am making a master pricing worksheet catalog. I am purchasing the same
products from mulitple vendors, all use the same product code as well. I have
set up 3 different vendors to compare prices on items. How do i put a formula
together on my master worksheet to scan these other 3 worksheets that are
identical to where i can have my master worksheet return the lowest price? So
if I type in product #1234 in the master sheet it will look at Sheet 1:3 and
return the lowest priced product?

 

[pdberger]

DPH --

I think you'll want to use a lookup function -- either VLOOKUP or HLOOKUP
depending on how the other three tables are organized. The help
documentation is pretty good for these functions. To make it easiest:

1) Create a Named Range on each subsidiary worksheet with columns of part
numbers, prices, descriptions, anything else you'll want to retrieve. These
named ranges might be called Supplier1, Supplier2, etc.

2) If the subsidiary-page source info is organized in columns with part#,
Description, price (in A, B, C), then the VLOOKUP function looks like:

A B
1 Part # Desc
2 12345 =VLOOKUP(A2,Supplier1,2,false)

3) To return the lowest price, you'd do something like:

=min(VLOOKUP(A2,Supplier1,3,false),VLOOKUP(A2,Supplier2,3,false), etc.)

HTH

 

[Bernard Liengme]

Lets assume you type the product code in A1
And that the three sheets have product codes in A1:A1000 and prices in
B1:B1000
And these are called Sheet1, Sheet2, Sheet3
You want the price returned to B1
=MIN(VLOOKUP(A1,Sheet1!A1:B10-00,2,FALSE),VLOOKUP(A1,Sheet2!A1:B10-00,2,FALSE),VLOOKUP(A1,Sheet3!A1:B10-00,2,FALSE)

Now tell us if all three sheets have every code? If not then we must add
some error checking along the lines of this untested code
=MIN(IF(ISNA(VLOOKUP(A1,Sheet1!A1:B10-00,2,FALSE)),1E6,VLOOKUP(A1,Sheet1!A1:B10-00,2,FALSE)),IF(ISNA(VLOOKUP(A1,Sheet2!A1:B10-00,2,FALSE)),1E6,VLOOKUP(A1,Sheet2!A1:B10-00,2,FALSE)),IF(ISNA(VLOOKUP(A1,Sheet3!A1:B10-00,2,FALSE)),1E6,VLOOKUP(A1,Sheet3!A1:B10-00,2,FALSE)))
best wishes

 

[Duke Carey]

Do you need the lowest price, or the identity of the lowest cost provider?

lowest price (assumes you name each vendors' price list range, and that each
range is 2 columns - item # & price):

=Min(lookup(item#, range1,2,0),lookup(item#, range2,2,0),lookup(item#,
range3,2,0))

 

[Duke Carey]

Lordy, that was a dumb omission: Not 'lookup', but VLOOKUP

=Min(Vlookup(item#, range1,2,0),Vlookup(item#, range2,2,0),Vlookup(item#,
range3,2,0))