How to return the lowest value of a series of columns when matching a record

**Ozzy24** · 05-09-2013, 08:28 AM

Hey Guys

I know it is probably possible to do this in excel 2010 with formulas, but I have no idea where to start! This is my scenario:

I have 2 sheets:

On sheet 1, I want to match column A with column A in sheet 2 (like a vlookup), however what I want to return is the tricky part:
Sheet 2 has 7 additional columns next to column A with numerical data in it, when a match is made from Sheet 1 Col A to Sheet 2 Col A, I want to return the HEADER of one of the additional 7 columns of data, that has the lowest value for that record.

For eg. if a match is made, the lowest value is then searched for in the additional 7 columns in that record, and the header for the column with the lowest value is returned.

Can anyone give me some guidance as to how to go about doing this?

Any help will be appreciated, thanks

**popipipo** · 05-09-2013, 08:36 AM

Can you post a xls sample that would help

**ChemistB** · 05-09-2013, 08:58 AM

I used this formula in Col B with data in Sheet2

=INDEX(Sheet2!$B$1:$H$1, MATCH(MIN(INDEX(Sheet2!$B$2:$H$9,MATCH(A2,Sheet2!$A$2:$A$9,0),)),INDEX(Sheet2!$B$2:$H$9,MATCH(A2,Sheet2!$A$2:$A$9,0),),0))

Is that what you are looking for?

**Ozzy24** · 05-10-2013, 01:36 AM

ChemistB that was EXACTLY what I was looking for, thanks so much man!!

**arlu1201** · 05-09-2013, 09:23 AM

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.