Formula for Horizontal and Vertical Search Combination

[budspwr]

I have a simple need for a formula that will find a minimum number horizontally and find the heading/header that numbers falls under.

Picture this table: A long column of food products in the first column. Columns 2 trough 8 have mixed prices for each product.

The top row heading for the 8 columns will be a company (Cells may be merged). Under this company row will be a different year for each of the columns 2 through 8.

There will be multiple companies with identical tables.

To the right of all these tables will be a column that will show the lowest cost of all the products regardless of company. Next to that will be a column that will be the year that low price was made. The next column will be the company that had the lowest price.

For minimum price, I had been using MIN(B3:H3).
For the other two requirements, I have tried several formulas and combination thereof but with out success. (HLOOKUP, VLOOKUP, MATCH, ADDRESS, FIND, CELL, etc.)

Is there someone that can give me a suggestion?

 

[Bob Greenblatt]

I have a simple need for a formula that will find a minimum number
horizontally and find the heading/header that numbers falls under.

Picture this table: A long column of food products in the first column.
Columns 2 trough 8 have mixed prices for each product.

The top row heading for the 8 columns will be a company (Cells may be merged).
Under this company row will be a different year for each of the columns 2
through 8.

There will be multiple companies with identical tables.

To the right of all these tables will be a column that will show the lowest
cost of all the products regardless of company. Next to that will be a column
that will be the year that low price was made. The next column will be the
company that had the lowest price.

For minimum price, I had been using MIN(B3:H3).
For the other two requirements, I have tried several formulas and combination
thereof but with out success. (HLOOKUP, VLOOKUP, MATCH, ADDRESS, FIND, CELL,
etc.)

Is there someone that can give me a suggestion?

First step is to unmerge the cells. You should go way out of your way to
NEVER use merged cells. There is always a better alternative. That said, you
may need to do this is a couple of steps. You have the minimum for the row.
Use that as a look up in the row to get the column index by using match.
Then use the index function using the value returned by the match to get the
column heading. It is possible to do this in one formula in a single cell,
but it may be easier to understand as several distinct formulas.

 

[budspwr]

Bob, thanks for your reply. I never know if anyone ever answers these questions.

I have tried several versions of your suggestion but without success.

Will you do this for me? Write out the suggested formula both independently and combined.

Here's a table example: in column A list several parts. In row B1 through J1, put a single (same) company. In row B2 through J2, list years 2002 through 2010. In cells B3 though J# (in my test sheet, I'm using 3 rows so # = 5) insert several and variable prices. In Column K3 though K5, I have MIN(B3:J3), etc. In column L3 through L5, I want to know the year. In column M3 through M5, I want to know the company.

There will be a dozen or so such tables and I will want to know a single source of a product at the lowest cost and in what year said product was purchased in each row of items in column A.

The following are formulas I've used:
INDEX(array,row_num,column_num) returns the value of a specified cell or array of cells within array.
INDEX(reference,row_num,column_num,area_num) returns a reference to specified cells within reference.
MATCH(lookup_value,lookup_array,match_type)

MATCH(MIN(B3:J3),MAX(B2:J2),0), I get an #N/A

Thanks again

Bud

 

[Bob Greenblatt]

Bob, thanks for your reply. I never know if anyone ever answers these
questions.

I have tried several versions of your suggestion but without success.

Will you do this for me? Write out the suggested formula both independently
and combined.

Here's a table example: in column A list several parts. In row B1 through J1,
put a single (same) company. In row B2 through J2, list years 2002 through
2010. In cells B3 though J# (in my test sheet, I'm using 3 rows so # = 5)
insert several and variable prices. In Column K3 though K5, I have MIN(B3:J3),
etc. In column L3 through L5, I want to know the year. In column M3 through
M5, I want to know the company.

There will be a dozen or so such tables and I will want to know a single
source of a product at the lowest cost and in what year said product was
purchased in each row of items in column A.

The following are formulas I've used:
INDEX(array,row_num,column_num) returns the value of a specified cell or array
of cells within array.
INDEX(reference,row_num,column_num,area_num) returns a reference to specified
cells within reference.
MATCH(lookup_value,lookup_array,match_type)

MATCH(MIN(B3:J3),MAX(B2:J2),0), I get an #N/A

You are getting an error because the second term in the above returns a
single value, not an array.

Thanks again

Bud

Your explanation is not at all clear to me. Send me an example worksheet and
explanation of what you are trying to find and I'll help.

 

[budspwr]

How do I get a sample sheet to you?

 

[budspwr]

Thanks Bob. Sample sent via your email.

 

[budspwr]

Bob;

Your suggestion worked, i.e., Index and Match. Had to do some trickery to get the company scenario to work properly.

If you would like a sample for your own edification, please let me know.

Bud