help with creating a formula

[grjnmiller]

Hi All

I don't know how to create this formula. So this is what i want to do

I have used the min formula to put the cheapest price in R3 so now i want to own the name of that supplier who had the cheapest price to go into Q3. How do i please this please.

thank you to everyone in advance.

[AliGW]

In R3:

=MIN(C3,F3,I3,L3,O3)

[JohnTopley]

Try

=INDEX($A$1:$O$1,MATCH(R3,$B3:$P3,0))

Note ranges are offset as you have merged cells for the supplier : ideally you should avoid using merged cells.

If you have two (or more) equal minimum prices, this will return the first supplier which matches.

[grjnmiller]

Hi John

Your formula worked once i removed the merged cells

thank you for you help.

[grjnmiller]

Try

=INDEX($A$1:$O$1,MATCH(R3,$B3:$P3,0))

Note ranges are offset as you have merged cells for the supplier : ideally you should avoid using merged cells.

If you have two (or more) equal minimum prices, this will return the first supplier which matches.

John

How can i get it to return more then then 1 supplier if this happens

new formula = =INDEX($C$1:$O$1,MATCH(R4,$C4:$O4,0))

thanks

Graeme

[JohnTopley]

Unmerging the cells (in this case) will still give the same results as the merged cells as the supplier is always in a cell one position (column) to the left of the price e.g B1 vs C2 .

If you have the answer you require could you please mark the thread as "solved" ("Thread Tools" at top of first post).

Thank you.

[JohnTopley]

If you have more than one supplier, how do want it to appear on the report: one the next row of the supplier column?

[grjnmiller]

If you have more than one supplier, how do want it to appear on the report: one the next row of the supplier column?

not sure as long as i can see the other suppliers names

[mucbeanz]

drop into column Q3
=IF(R3=C4,$B$1,IF(R3=F4,$E$1,IF(R3=I4,$H$1,IF(R3=L4,$K$1,IF(R3=O4,$N$1," "))))) this will give the proper response for the Min Function result in Column R3

drop into Column R3
=MIN(C3,F3,I4,L3,O3)


might suggest making your sheet a table under the code price % dif titles,will make it so the formulas auto-populate

Enjoy !!

(an easier way to do this would be to script it in VBA to run everytime you add a line it will run the formulas more definitely or accurately just from the nature of how the code can be defined. Your going to get an error or false reading if 2 prices are the same this way but it should work just fine for now)

[JohnTopley]

Try in Q3

=IFERROR(INDEX($A$1:$P$1,SMALL(IF($A$3:$P$3=$R$3,COLUMN($A$1:$P$1)-COLUMN($A$1),""),ROWS($A$1:A1))),"")

Enter with CTRL+SHIFT+ENTER

Copy down

[grjnmiller]

Hi JohnTopley

How do i make a formula to find the 2nd cheapest price

thanks

Graeme

[JeteMc]

Paste this in N3:

Please Login or Register  to view this content.

Let me know if you have any questions.

[JohnTopley]

This will give supplier (of 2nd cheapest)

=INDEX($B$1:$J$1,MATCH($N3,$B3:$J3,0))

[grjnmiller]

thanks johntopley
sorry i also forgot to ask what formula i put in the "price column" to put the 2nd cheapest proice

[grjnmiller]

If you have more than one supplier, how do want it to appear on the report: one the next row of the supplier column?

sorry to ask this but I now need to know the answer to your question.

as you can see I now have 2 suppliers with the same price. I need one in each column

[AliGW]

@SMJ2005 - Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.