• 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.

Lookup Multiple Columns

Kenshin

Member
Hi Ninjaz need assitance again sorry to bother you guys again, please ghelp thank you
 

Attachments

  • Sample.xlsx
    8.4 KB · Views: 12
Older versions of Excel, array-enter (use Ctrl+Shift+Enter instead of plain Enter to commit the following formula to the sheet) into cells F5:F8 at once:
Code:
=IFERROR(INDEX(A2:A5,SMALL(IF(((B2:B5=F2)+(C2:C5=F2)+(D2:D5=F2))>0,ROW(A2:A5)-ROW(A2)+1),ROW(A2:A5)-ROW(A2)+1),0),"")
 
Last edited:
cell F5:
Code:
=FILTER(A2:A5,BYROW(B2:D5,LAMBDA(a,OR(a=F2))))
I like it. It is such a relief [for me] seeing movement away from the traditional spreadsheet. I tried a variation which carries the test against the criterion as a 2D array formula and only then breaks it down for the Lambda function equivalent of OR, using BYROW.
Code:
= FILTER(Name,
    BYROW(Products=Criteria, ORλ))
    
where ORλ is given by:
= LAMBDA(x,OR(x))
 
Hi to all!

Another option, using traditional Excel array formulas:
[F5] : =IFERROR(INDEX(A$2:A$5,AGGREGATE(15,6,ROW(A$2:A$5)-ROW(A$1)/MMULT(COUNTIF(F$2,B$2:D$5),{1;1;1}),ROWS(F$5:F5))),"")
Drag it down. Blessings!
 
Back
Top