Copying text value based on long (comlex) calculation

[gezuvor]

I am not sure how to even search for what I want to do, so please forgive me if my first post has been discussed and resolved ad naseum in the past...

Here is my situation:
- I have a workbook that contains multiple sheets.
- Each sheet contains auto parts from different suppliers, sorted by part number.
- Some suppliers have parts that overlap with each other and the various sheets are updated as pricing and availabilty of parts change.
- I am using a combination of the VLOOKUP and MIN functions to determine which supplier has lowest cost based on a comparison of part numbers and price across each supplier.
- The first sheet is a summary and contains all of the part numbers (entered manually), as well as the lowest cost from any supplier's worksheet (as calculated above).
- Cell A1 of each supplier sheet has the company name in it.

My question is this:
- For each part number listed on the summary page, I want to display the name of the supplier who has the lowest cost for the part in question.

Thanks in advance...

[Forrest]

Have you combined all the Vlookup functions into one formula, or are you
comparing the results of formulas in multiple cells on your summary sheet and
chosing the lowest value? If the particular value you use in the summary is
in a cell related only to one sheet, then you can use an IF function that
says if a particualar cell has the minimum value then the result is equal to
A1 on a sheet.

"gezuvor" wrote:

>
> I am not sure how to even search for what I want to do, so please
> forgive me if my first post has been discussed and resolved ad naseum
> in the past...
>
> Here is my situation:
> - I have a workbook that contains multiple sheets.
> - Each sheet contains auto parts from different suppliers, sorted by
> part number.
> - Some suppliers have parts that overlap with each other and the
> various sheets are updated as pricing and availabilty of parts change.
> - I am using a combination of the VLOOKUP and MIN functions to
> determine which supplier has lowest cost based on a comparison of part
> numbers and price across each supplier.
> - The first sheet is a summary and contains all of the part numbers
> (entered manually), as well as the lowest cost from any supplier's
> worksheet (as calculated above).
> - Cell A1 of each supplier sheet has the company name in it.
>
> My question is this:
> - For each part number listed on the summary page, I want to display
> the name of the supplier who has the lowest cost for the part in
> question.
>
> Thanks in advance...
>
>
> --
> gezuvor
> ------------------------------------------------------------------------
> gezuvor's Profile: http://www.excelforum.com/member.php...o&userid=30383
> View this thread: http://www.excelforum.com/showthread...hreadid=500452
>
>

[gezuvor]

I have combined all of the VLOOKUP calculations into one formula, but I like your idea. It's not as elegant, but it would be a quick way to accomplish my goal.

Here is the long formula, genericized for ease of reading...

=IF(ISERROR(MAX(VLOOKUP($B6,'Supplier1'!$B:$D,2,FALSE),(VLOOKUP($B6,'Supplier2'!$B:$D,2,FALSE)))*'DATA - General'!$C$3),"",(MAX(VLOOKUP($B6,'Supplier1'!$B:$D,2,FALSE),(VLOOKUP($B6,'Supplier2'!$B:$D,2,FALSE))))*'DATA - General'!$C$3)

I am struggling to find a way to extract the supplier (worksheet) name from this formula to do the relatively simple look-up required to include "Supplier1" in an adjacent cell...