Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 Rook Dec 21 '12 at 9:26
add comment

1 Answer 1

up vote 4 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
1  
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
1  
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. –  user433531 Dec 21 '12 at 14:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.