0

I seem to have run into an interesting situation...

I'm currently creating an Excel spreadsheet whereby I have a table of multiple value. This table's most important columns are "Size", "Vendor" and "Offer", and the table is used as a mixture of vendor price lists. This means that there are more than one vendor offer for one particular item.

The spreadsheet's requirement is to lookup the lowest offer of a product, display that price, and then tell the user who the Vendor is to whom that price belongs.

Here's the table:

enter image description here

Assume that the top-left cell is A1

The User inputs data into the table, and based on that, the best offers are calculated into cell ranges E2:F3, where Column E is the best price, and Column F is the Vendor to whom the value in Column E belongs to.

Column E's formula is:

{=MIN(IF(Table2[Size]=$D$2,Table2[Offer]))}  // This is an array-formula

This looks at the Column "Size" in the Table, creates an array based on that answers, and adds the offer price in each true result into the array. The Formula then checks that temp array, and then determines the minimum in that array. This formula works.

Here's where the issue comes in...

In Column F, the Vendor needs to be displayed. There's no values to be calculated. I am not able to get the value working. What I have been able to do, is to at least get the Cell Address. I can't get the value of the cell to display.

Column F's current formula is:

{=ADDRESS(MATCH(MIN(IF(Table2[Size]=$D$2,Table2[Offer])), Table2[Offer], 0), 2, 1)}   // This is an Array Formula

The outcome of this formula is a correct column, but incorrect row. In the live spreadsheet, my values are out by 8 rows starting at 1. This is because the formula looks at the row number within the temp array, which is the correct answer in the scope of the temp array, but not correct in the scope of the spreadsheet, so manipulation is required to add 8 to the Row number in future use.

A sample of the answer would be something like $B$2, but the true answer is $B$10.

This is the one problem. The next problem is getting the value of the cell at that address.

For this, I looked at using in a separate cell to test the theory of ops:

  1. =CELL("contents", $manual_answer_of_above) → Result = Correct
  2. =INDIRECT(ADDRESS($manual_row_no, $manual_col_no)) → Result = Correct
  3. =INDIRECT(ADDRESS(ROW(CELL("contents", $cell_with_address), COLUMN(CELL("contents", $cell_with_address)), 2, 1)) → Result = Formula not Accepted

What am I missing? Why won't my formulas allow me to get the correct Vendor to be displayed?

0

If you have Office 365 Excel, this will return the value:

=INDEX(Table2[Vendor],MATCH(MINIFS(Table2[Offer],Table2[Size],$D$2),Table2[Offer],0))

Else if you do not then AGGREGATE can Replace the MINIFS:

=INDEX(Table2[Vendor],MATCH(AGGREGATE(15,6,Table2[Offer]/(Table2[Size]=$D$2),1),Table2[Offer],0))

Neither require the use of an array entry. Also, both avoid the use of volatile functions like CELLS,INDIRECT, or ADDRESS.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.