Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a sample price and I want to find the company that offers the lowest or an equal price to that sample price in the same row and put their name in the "Company Name" column. I tried to use IF to compare and but it results in "Too many arguments" error with large table. Can anybody tell me how to achieve it?

Price   Company A   Company B   Company C    Company Name
5       5           6           7           Company A
3       9           3           8           Company B
share|improve this question
Please share your code (edit your original post and add it), it may be easier to fix that instead of coming up with new ways. – Dave Dec 21 '12 at 9:26
up vote 6 down vote accepted

Ok, lets go. Your question says "lowest or equal" to the sample price, but don't say what to do if the price is greater than the sample price. So I am just going with the lowest:

This finds the lowest price (I am assuming the company columns are B-D):

=min(B2:d2)            (equals 5 for your first row)

This finds the column position of the lowest price:

=match(min(b2:d2), b2:d2,0)      (equals 2 because that is the position of the lowest (5) in your first row)

This returns the value within the first row that has the column with the lowest value:

=index(b$1:d$1 ,match(min(b2:d2), b2:d2,0)))     (returns "Company A" - the contents of that cell) 

So the last formula is all you need, the previous steps are just to explain how we got there.

share|improve this answer
No problem with the MATCH part but better to use INDEX to then get the value from top row, i.e. =INDEX(B$1:D$1,MATCH(MIN(B2:D2),B2:D2,0)) – barry houdini Dec 21 '12 at 12:28
Oh yeah, much cleaner. Feel free to edit it into the answer. – Paul Dec 21 '12 at 12:53
What an excellent solution! Thank you very much for your help, that's exactly what I need. – Teiv Dec 21 '12 at 14:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .