0

What I have :

  • A table with columns (the column number has non unique numbers)
  • Some numbers, but don't mind this, it is only because I wanted a spilled list
  • A spilled list on F3 (the filter list), of any lenght

enter image description here

What I want :

  • A Result table that only shows the rows whose number is in the spilled list.

Currently, the formula I used in Result table is :

=FILTER(Table1;(Table1[Number]=F3)+(Table1[Number]=F4))

The problem is that I need the spilled list to be as long as 10 or 20 cells, so my idea was not very efficient.

I tried =FILTER(Table1;(Table1[Number]=F3#)) but this doesn't work. (I thought about SQL "IN" function, because if I could try for each cell of the column if it is IN the list, it would work?)

I also tried =XLOOKUP(F3#;Table1[Number];Table1) but it gives me only Bob and Billy, not Martin even though Martin has number 1 (I think because XLOOKUP only shows the first match for each value).

Using SQL "IN" Function in Excel don't work for what I want (because only I have a column and not just 1 cell).

Thank you very much for your time, if you need any additionnal information please ask !

Improve this question

2 Answers 2

Reset to default
1

New answer based on updated information. Turns out you were on the right lines with Filter originally, just needed to use an IF() in that second argument. Try

=FILTER(Table1,IF(COUNTIF(F3#,Table1[Number])>0,TRUE,FALSE))

and let me know if it works for you.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Use Vlookup - VLOOKUP(F3#, Table1, 2, FALSE) and let it spill next to your F3# spill range. Edit the 2 in the formula appropriate to tables in your column, and you'll need a separate spilling Vlookup formula for each column you want to return.

Improve this answer

5 Comments

Hello, thanks for your answer. I just tried this formula, but I only get "#N/A". Does it work for you ? I also tried with XLOOKUP, but I can't get it done
It worked fine for me, what did you try with your XLOOKUP formula? Could you try an XLOOKUP and put something in the if_not_found parameter to check whether it's not finding the values or if the error is something different?
Hello, the thing is that VLOOKUP and XLOOKUP can match only 1 result per search value, so it returns only 1 row corresponding to number 1 and 1 row for number 3, even if I have 2 number 1. (for example, XLOOKUP(1;Table1[Number];Table1[Firstname]) returns only Bob, but i want it to return both Bob and Martin, since they are both number 1). Do you have any idea on how to handle this ? Thanks so much for your time !
Ah, I was working on the assumption that your IDs were unique. I'm afraid non-unique IDs would require a bit of a different approach, I'll try and work it out and post as a separate answer if I get anywhere.
Oh, thanks so much ! Yes you're right, I'll add that my IDs are not unique in the description. Tell me if you need some more details !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.