• 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 formula with matching multiple columns

N

Nandakumar

Member
Hi Experts,

I need a help to identify lookup for the month of travel of a particular person to specific city.Given below the table and attached excelsheet as well.
Please help

1752858276524.png
 

Attachments

  • 1752858126778.png
    1752858126778.png
    45.7 KB · Views: 10
  • Chandoo_18Jul25.xlsx
    10.2 KB · Views: 7
Hi @Nandakumar. Try this array formula (after entering, press Ctrl+Shift+Enter, not just Enter) if you have Excel 365/2021 (or Excel with FILTER/TEXTJOIN support): C7 =TEXTJOIN(", ", TRUE, FILTER(C$11:M$11, INDEX(C12:M22, MATCH(C3, B12:B22, 0), 0) = C5))

Or if you have an old version of Excel (without FILTER, TEXTJOIN). You can use an array formula (after entering, press Ctrl+Shift+Enter, not just Enter): C7 =INDEX(C$11:M$11, MATCH(TRUE, INDEX((INDEX(C12:M22, MATCH(C3, B12:B22, 0), 0)=C5), 0), 0))
These formulas will work for the example file you provided. I hope these formulas will solve your problem. Good luck.
 
MikeVol said:
Hi @Nandakumar. Try this array formula (after entering, press Ctrl+Shift+Enter, not just Enter) if you have Excel 365/2021 (or Excel with FILTER/TEXTJOIN support): C7 =TEXTJOIN(", ", TRUE, FILTER(C$11:M$11, INDEX(C12:M22, MATCH(C3, B12:B22, 0), 0) = C5))

Or if you have an old version of Excel (without FILTER, TEXTJOIN). You can use an array formula (after entering, press Ctrl+Shift+Enter, not just Enter): C7 =INDEX(C$11:M$11, MATCH(TRUE, INDEX((INDEX(C12:M22, MATCH(C3, B12:B22, 0), 0)=C5), 0), 0))
These formulas will work for the example file you provided. I hope these formulas will solve your problem. Good luck.
Click to expand...
Hi @MikeVol - it works.Thank you so much
 
An alternative is to create parameter queries in Power Query. See attached. Fill in the parameters and then on the data tab, select Data, RefreshAll
 

Attachments

  • Chandoo_18Jul25.xlsx
    20.4 KB · Views: 4
MikeVol said:
Hi @Nandakumar. Try this array formula (after entering, press Ctrl+Shift+Enter, not just Enter) if you have Excel 365/2021 (or Excel with FILTER/TEXTJOIN support): C7 =TEXTJOIN(", ", TRUE, FILTER(C$11:M$11, INDEX(C12:M22, MATCH(C3, B12:B22, 0), 0) = C5))

Or if you have an old version of Excel (without FILTER, TEXTJOIN). You can use an array formula (after entering, press Ctrl+Shift+Enter, not just Enter): C7 =INDEX(C$11:M$11, MATCH(TRUE, INDEX((INDEX(C12:M22, MATCH(C3, B12:B22, 0), 0)=C5), 0), 0))
These formulas will work for the example file you provided. I hope these formulas will solve your problem. Good luck.
Click to expand...
Hi @MikeVol - I need help on the formula.If i want to change the scenario if name repeats.Attached excel sheet for reference
 

Attachments

  • Chandoo_18Jul25.xlsx
    11.2 KB · Views: 8
Code: 
=TOROW(FILTER(IF(C13:K23=C5,C12:K12,NA()),B13:B23=C3),3)

1753521456886.png

In the case where the same name visits the same city in the same month more than once you may want to wrap the result in UNIQUE:
Code: 
=UNIQUE(TOROW(FILTER(IF(C13:K23=C5,C12:K12,NA()),B13:B23=C3),3),TRUE)
 
Last edited:
Hi,

I'm sending you my solution, I hope it helps.
The formula is in cell C10.

If you liked it, don't forget to give it a thumbs up!
 

Attachments

  • Chandoo_18Jul25 v2.xlsx
    11.8 KB · Views: 3
Nandakumar said:
I tweaked the data,but answer is coming in ascending based on month instead on actual basis.Attached excel sheet with expected answer.
Click to expand...
Take the ,TRUE out that I highlighted with a red oblong in my msg#11, and if you want to see repeating months then take out the UNIQUE wrapper too.
 
Hi - how to change the formula to specific cells instead of dynamic arrays.
For example i want to put the formula for D7,E7,F7 instead of automatically taking the formulas to other columns?
Attached excel sheet for your reference
 

Attachments

  • Chandoo_18Jul25.xlsx
    11.6 KB · Views: 3
Nandakumar said:
Hi - how to change the formula to specific cells instead of dynamic arrays.
For example i want to put the formula for D7,E7,F7 instead of automatically taking the formulas to other columns?
Attached excel sheet for your reference
Click to expand...
Why?!

=INDEX(TOROW(FILTER(IF($C$13:$K$23=C5,$C$12:$K$12,NA()),$B$13:$B$23=C3),3),1)
will give you the first result.

=INDEX(TOROW(FILTER(IF($C$13:$K$23=C5,$C$12:$K$12,NA()),$B$13:$B$23=C3),3),2)
will give you the second result etc.
 
You must log in or register to reply here.
Back
Top