• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match with Greater than or Less than condition

Hi,

I want to pull the names of all the entries where the data is less than equal to 3, with this formula it should return all the entries even if there are any duplicate entries.

Details are in the attached spreadsheet.

Regards,
Manish
 

Attachments

  • Index with greater than or less than condition.xlsx
    114.3 KB · Views: 9
A Power Query Solution

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Store Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <= 3)
in
    #"Filtered Rows"
 

Attachments

  • Index with greater than or less than condition.xlsx
    206.1 KB · Views: 3
Try,

In cell CJD3, formula copied down:

=TEXTJOIN(", ",,IF(B3:CJB3<=3,B$2:CJB$2,""))

Remark: INDEX+MATCH function only return single result, for multiple result you need TEXTJOIN function.

Regards
 
Back
Top