Easier Formula

[GYoder]

Is there an easier way to write this formula. I use it in a spread sheet to
pick out the lowest price and associated vendor name in the result cells.

Thanks
George

 

[PCLIVE]

What's the formula?

 

[GYoder]

Sorry, I didn't send the formula. Here it is.

=IF(J8=" ","
",IF(J8=M8,M7,IF(J8=N8,N7,IF(J8=O8,O7,IF(J8=P8,P7,IF(J8=Q8,Q7,IF(J8=R8,R7,IF(J8=S8,S7,T7))))))))

 

[Rick Rothstein \(MVP - VB\)]

I'm guessing J8 contains the minimum value for the range M8:S8. Therefore,
this formula will give you the "header" text in the cell above it...

=INDEX(M7:S7,MATCH(J8,M8:S8))

Rick

 

[Sandy Mann]

Rick,

Being as the OP is making a comparison in his formula would it not be better
to have the third argument in the MATCH() function?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

It will not matter for the OP's given condition, assuming I am right and the
value in J8 is the calculated MIN for the range (the only thing that makes
sense for his stated conditions), as the default Match Type of 1 (which
finds the largest value that is less than or equal to Lookup Value) will
always locate the exact value (due to its "or equal" case). However, as a
matter of style, always including it can never be argued with.

Rick

 

[Sandy Mann]

It will not matter for the OP's given condition, assuming I am right and
the

value in J8 is the calculated MIN for the range

Are you sure?

With 6, 7, 8, 9, 1, 7 & 10 in M:S8, =INDEX(M7:S7,MATCH(J8,M8:S8,0)) returns
#N/A but with a third augument of zero or FALSE it returns the label in Q7.

I also just noticed that it should be extended to T7 & T8

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

LOL... that what I get for short-cutting the process and creating
quick/sequential data to test on. Right you are... I should have used 0 for
the 3rd argument. Thanks for sticking with me on this until you beat it into
my thick skull.<g>

Rick

 

[Rick Rothstein \(MVP - VB\)]

As Sandy Mann has rightfully pointed out, I left out an important argument
from my MATCH statement. The formula you should use is this...

=INDEX(M7:T7,MATCH(J8,M8:T8,0))

Also note the correction from and ending column of S to the correct ending
column of T (also pointed out to me by Sandy elsewhere in this thread).

Rick