# Lookup MIN in 3 separate ranges, return col. heading

1. ## Lookup MIN in 3 separate ranges, return col. heading

Found formula to find lowest price in separate ranges, need to return column header of the lowest price.
Worksheet attached.

BestPriceExcelIndexMatch-1.xlsx

Many thanks for ideas.

Pete

2. ## Re: Lookup MIN in 3 separate ranges, return col. heading

I don't like manual reconfiguring of formulas, so I rewrote your MIN.

B3:

=MIN(IF((\$J\$2:\$R\$2=B\$2)*(\$I\$5:\$I\$9=\$A3)*(\$J\$5:\$R\$9)<>0,(\$J\$2:\$R\$2=B\$2)*(\$I\$5:\$I\$9=\$A3)*(\$J\$5:\$R\$9)))

(array formula)

D3:

=LOOKUP(2,1/((\$J\$2:\$R\$2=B\$2)*OFFSET(\$J\$4:\$R\$4,MATCH(\$A3,\$I\$5:\$I\$9,0),0)=B3),\$J\$3:\$R\$3)

Copy down and over as needed.

3. ## Re: Lookup MIN in 3 separate ranges, return col. heading

I plugged in the suggested formulas; B3 returned a - (dash), Could you revisit, or upload a copy of the formula/workbook that isn't misbehaving?

Many thanks..

Pete

4. ## Re: Lookup MIN in 3 separate ranges, return col. heading

I apologize for not remembering to add:

The Value you have in B2 is doesn't match the values of J2:R2.

Change it to "Qty 1" with a space like you have in D2 and F2, instead of "Qty1"

Then the formula will work.

5. ## Re: Lookup MIN in 3 separate ranges, return col. heading

You're my new best friend! And a daysaver too.

Thanks so much.

Pete

6. ## Re: Lookup MIN in 3 separate ranges, return col. heading

No problem. Always like a good challenge.

If that takes care of everything, you should mark the thread as Solved, under Thread Tools at the top.

8. ## Re: Lookup MIN in 3 separate ranges, return col. heading

Ace_XL, that works beautifully as well! Thank you for the work you did on it.

Pete

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1