Solved

Excel Formula Question

Posted on 2011-02-15
4
226 Views
Last Modified: 2012-08-14
I have an excel spreadsheet that has three columns of data and I need to return the value in the third column based on criteria in the other two.  For example:

Col1    Col2     Col3        Col4
1         2          asdf        zxcv
1         2          qwer       zxcv
1         1          zxcv        zxcv
1         2          asdd        zxcv
2         2          qwds       qw32
2         1          qw32       qw32
3         1          asert        asert
3         2          qwed       asert
3         2          qwxa       asert
3         2          saew       asert

Column 4 is where the formula should go and those are the values that should be returned.  The idea being that the formula will evaluate column2 (looking for the minimum value) where the values in column1 are equal and place the corresponding value in column 3 in column 4.

Thanks.
0
Comment
Question by:palacesports
  • 2
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 34900413
You can use this array formula in D2 copied down

=INDEX(C$2:C$11,MATCH(1,(B$2:B$11=MIN(IF(A$2:A$11=A2,B$2:B$11)))*(A$2:A$11=A2),0))

confirmed with CTRL+SHIFT+ENTER

If the minimum value is duplicated for an entry in column A the formula will return the first value in column C for those minimums.....

see attached

regards, barry
26823496.xls
0
 

Author Comment

by:palacesports
ID: 34901512
Barry,

Thanks for the quick response!  That is an impressive formula.  For some reason I'm receiving a "value not available error" in my spreadsheet with this formula.  I've stepped through the formula and it seems to evaluate how I would expect it to, but the end result is "INDEX($C$2:$C:$C$11,#N/A)" and I'm not sure why.  The formula works like a champ in your spreadsheet though.  Any idea why it would behave this way?

Thanks.
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
ID: 34901984
I assume you used CTRL+SHIFT+ENTER

If you did and you still get #N/A error then that's probably from the match function. There are a number of possible causes, are you sure column B is numeric (values could be formatted as text). Test by using

=COUNT(B2:B11)

That counts numbers - you ought to get 10 for 10 numbers if you get zero (or fewer than 10) then your numbers are probably formatted as text. Try converting to numeric like this

Select column B > data > text to columns > finish

If that doesn't work then can you attach the workbook....or a sample?

regards, barry
0
 

Author Comment

by:palacesports
ID: 34908323
Thanks Barry.  It was the CTRL+SHIFT+ENTER validation step that I messed up.  I didn't validate the formula after I modified it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Score Formula 5 49
Vlookup nonexact IP address match 32 51
Excel Conditional Formatting in a Macro 4 26
How do I crate a Pivot table in Excel 2 10
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now