0

Trying to automate some part on excel for my work, I tried Index and Match but it doesn't provide me what I have in mind and I am quiet confuse what formula should I used.

I have a sheet in excel containing a list of combinations and I have mark "N" on the cell which is a bad combination.

For example, this is the sheet containing the data i want to query:

Source data

And this is the result I want to achieve:

Data with expected result

In general I wanted to display the bad combination(N) of Potato(B10), Apple(C10),Orange(D10),Mango(E10),Lemon(F10) on B11 to F10.

For example for Potato(B10), I wanted to display what this the bad combination(N)

Improve this question
3
  • Ah yes, just random data I want to enter for reference.. pls don't mind it. I just renamed it to LIST instead to avoid distraction. Commented Aug 18, 2022 at 15:09
  • What version do you have? Commented Aug 18, 2022 at 15:15
  • I have Microsoft® Excel® for Microsoft 365 Commented Aug 18, 2022 at 15:18

1 Answer 1

Reset to default
0

Use two FILTER()s the first to return the correct ROW and the second to filter on N:

=TRANSPOSE(FILTER($B$1:$F$1,FILTER($B$2:$F$6,$A$2:$A$6=B10)="N"))

Put that in B11 and copy over

enter image description here

Improve this answer
4
  • Wow, this works great. Let me test it on other data and get back to you on the part I need clarification. Thank you. Commented Aug 18, 2022 at 15:33
  • The solution was awesome. I have added another scenario on the post, how I should achieve it? thanks for the time. Commented Aug 18, 2022 at 16:22
  • 2
    @JohnEnglatiera that is not how this forum works. once you get an answer adding additional needs it not correct. Instead you mark the answer that answered the first question correct by clicking on the check mark by the answer and then you ask a new question, you can always refer back to this one to help the readers on the second get a better understanding. But by changing the question you open up my answer for down votes. Commented Aug 18, 2022 at 16:27
  • Sounds good, noted Scott. I have marked your response as an answer Commented Aug 18, 2022 at 16:52

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.