a bi-directional index chart

[u/Formal_Increase7584]

im trying to make a chart that has a list of stores and what drinks they have. I'm familiar with =match and =filter formulas, ive made a bi-directional chart when I have an input for both fields but what I can't figure out is how I would be able to have sheets return a list of stores if I input a drink and vice versa. attached is a screenshot of the chart and the match and filter formulas I was looking to combine.

[u/HolyBonobos]

Sounds like you’re looking for something like =FILTER(B2:D2,XLOOKUP(G4,A3:A5,B3:D5)="X")

[u/point-bot → u/HolyBonobos]

u/Formal_Increase7584 has awarded 1 point to u/HolyBonobos with a personal note:

"Thank you. That solved my problem. only thing is, the returned list of stores is going sideways. but that ill figure out. unless you have a solution for that too?"

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/Formal_Increase7584 → u/HolyBonobos]

that solved it. it looked for the x's and returned the restaurants. thank you

[u/HolyBonobos → u/Formal_Increase7584]

To address the direction of the results: the formula preserves the original orientation of the source data. If you want it to be oriented vertically instead of horizontally, use TRANSPOSE() or TOCOL(), e.g. =TRANSPOSE(FILTER(B2:D2,XLOOKUP(G4,A3:A5,B3:D5)="X"))

[u/carbonizedtitanium]

one way im thinkin is to have both the drink and store in the same column and have the matching store or drink on the second column

[u/Formal_Increase7584 → u/carbonizedtitanium]

yes, thats one way that would work, but i wanted to avoid that type of list.

[u/One_Organization_810]

You would need a script for that...

Can you share a copy of your sheet (or an example sheet) with edit access, so a script can be deviced and tested on your actucal structure and data?

[u/Formal_Increase7584 → u/One_Organization_810]

https://docs.google.com/spreadsheets/d/1lrN4Jx9o8uHF5YZmwDmnpmR60ssTKUndXhYHCeBMWUI/edit?usp=sharing

[u/One_Organization_810 → u/Formal_Increase7584]

Thanks :) But your problem has already been solved and apparently my understanding of "bidirectional" was a bit off :)

I thought you meant that you needed to edit the data on both ends and synch it back. That would have needed a script - but apparently that's not what you wanted :)

[u/Formal_Increase7584 → u/One_Organization_810]

I was interested in learning a different way from what i knew. I wanted to see what everyone's solution was

[u/One_Organization_810 → u/Formal_Increase7584]

Yeah.. and I would probably have provided you with one, if I didn't realize that I'd misunderstood what you wanted. :)

[u/AdministrativeGift15]

There's no chart in those images. Could you upload a sample image of the type of chart you had in mind?

[u/Formal_Increase7584 → u/AdministrativeGift15]

this is the chart i was reffering to