CODEWITHSUNDEEP
excelexcel-formula
Fulvio
Fulvio

Reputation: 31

Return all the cell whose next one contains a value range

I need to return in a cell all the cell values where the cell next to it contains a value range.

For example, if I have a table like this:

|Name  |Evaluation
|------|------
| John | 3 
| Sue  | 4 
| Jim  | 2 
| Andy | 6 
| Tim  | 1 
| Bruce| 4

I'm looking for a formula to have all the names whose evaluation is >= 4, so, if applied to the table it should give as output in a single cell:

Sue
Andy
Bruce

I've already tried VLOOKUP, INDEX, MATCH and FIND functions but they all return a single value (the first cell that match) and not all of them.

If possible, I'm looking for an Excel Formula and not for VBA (this way I can share it easily with my working group that, as myself, is not very proficient in VBA).

Thank you very much!

Upvotes: 2

Views: 341

Answers (2)

Fulvio
Fulvio

Reputation: 31

I've solved the issue by using (partially) the solution posted by dhS and a support table.

I've created the support table of the same height of the original one. This table goes from F1 till F120 (the end of the original table). In the first cell i've used the formula =IF(B1>=4;$B1;"") In all the subsequent cells (from the second to the final one) =IF(B2>=4;IF(F1="";B2;F1&CHAR(10)&B2);F1)

This way, in the last cell there will be all the names separated by a return (CHAR(10)). To anyone who want to use this solution, remember to enable the Wrap Text option on the cell, otherwise you won't be able to visualize the returns.

Thanks to everybody for the help you gave me.

Upvotes: 0

dhS
dhS

Reputation: 3694

=IF(B1>=4,A1,"") write the command in c1 column and drag the C1 column to the end of the column till the end name
(assuming you write first name in A1 column and Evaluation in B1 )

Upvotes: 1

Related Questions