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]

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...