Return Value and Related Value Based On Specified Criteria

0

I want to return a value that matches a certain criteria, and at the same time, return a related value that is tied to the referenced value.

For example, there is a spreadsheet of data for timekeeping at a software company. Column A has the name of an Application sold by the company, Column B has the version number of the application being worked on, and Column C has the Number of hours:minutes worked.

There will be multiple entires in Column B that have the same value in Column A. To compile a list of each instance, I used an array like this:

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

What I am trying to do now is also display the value of the corresponding Column C associated with each value of Column B. (i.e. if cell B34 is a match, also display C34 next to it.) I thought I could use the above array for both sets of data and it would display both of them in the same order, making them match up properly. That is not the case, for some reason.

Am I thinking too hard and missing an obvious method? Appreciate any assistance. Thank you!

Answer
Discuss

Discussion

I can't quite imagine the table that you try to produce. Not knowing what data you want to show to the left of the hours, or what is in E1, doesn't help. Anyway, have you considered using a Pivot table for that?
Variatus (rep: 2025) Jan 24, '18 at 9:01 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login