• 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

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

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

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:
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

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.
 
p45cal said:
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.
Click to expand...
Thanks you so much :-) it works
 
You must log in or register to reply here.
Back
Top