# Indirect within a Minimum

1. ## Indirect within a Minimum

I have a script

=MATCH(H2,INDIRECT("'SALES'!A"&R2&":CC"&R2),0)

to produce the column number of the minimum value on the SALES sheet. The issue is that when I change the minimum value it produces a completely wrong number and reads the wrong column. How would I get this to read only specific columns and find the minimum: L, W, AH, AS.

2. ## Re: Indirect within a Minimum

First, we can do this without indirect...

This will be the equivelent of the Indirect portion of your formula
INDEX('SALES'!A:CC,R2,0)

So we have
=MATCH(H2,INDEX('SALES'!\$A:\$CC,R2,0),0)

Now, I don't see how your formula finds the Minimum value in the row...
Does H2 have a MIN formula in it, referring to the same range?

3. ## Re: Indirect within a Minimum

Yes H2 does have a MIN formula within it and it solves it correctly when the minimum is changed in the Sales Sheet

4. ## Re: Indirect within a Minimum

Originally Posted by ac847
Yes H2 does have a MIN formula within it and it solves it correctly when the minimum is changed in the Sales Sheet
So it solves it correctly then?
What is the issue?

Can you post a sample book?

5. ## Re: Indirect within a Minimum

=MIN('SALES'!L65,'SALES'!W65,'SALES'!AH65,'SALES'!AS65) is H2. There is no issue with H2. The issue is that in this spreadsheet I need the minimum value to be solved and then produce the cell that is 6 to the left of the minimum. I am able to get the cell 6 to the left produced the first time, but when I change the minimum value, it screws the whole value up. I need it to search for the minimum values in specific cells.

6. ## Re: Indirect within a Minimum

Lost without an example (Go Advanced>Manage Attachments)

What is in R2? and why?
If you always want the value 6 to the left of the minimum (and that minimum value isn't duplicated elsewhere in row 65),
=INDEX(Sales!\$F\$65:\$AS\$65, MATCH(H2, Sales!\$L\$65:\$AS\$65,0))

7. ## Re: Indirect within a Minimum

post a sample book, there must be an easier way.

Do you have Headers in Row 1 that might indicate which cells to look at ?

There are currently 1 users browsing this thread. (0 members and 1 guests)