I want to lookup data in other sheets and put them each in a seperate row in the same col

Hello Everyone,

I wanted to ask if a certain Excel task can be done with normal functions or it must be done with VBA

I want in this sheet to write in cell B2 to Check the value in A2 and finds any similar value in Column L in both "Sponsors" and "School" Sheet and return the value of the corresponding cell in column Q in either sheets and if the cell in column Q is empty then take the corresponding value in column P and if there are multiple values for the same entry(value in column A) then i want it to be placed each in a seperate row in the same column that i will type the function in.

P.S: I've attached the Excel file

Hopefully you can help me,
Thanks!

2023 Sponsor data (4) (1) (2).xlsx

Administrative Note:

There is NO 2022 Excel product - what do you have? This is important.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to consider amending your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Guideline #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

Thanks. I am afraid I'm not following what you want - hopefully someone else will.

What do you expect to see? Where do you expect to see it? Which sheet am I supposed to be looking at?

If it is Collected data.. what is the existing formula doing incorrectly? What are the expected answers?

Please amend your file to show at least ONE example where your current formula DOES NOT work... and tell us what the correct answer is.

Transform Sponsor & Schools data to structured tables (Called Sponsor and School....). These will adjust automatically if/when new lines are added.

Delete expected results from columns A & B.

A2:
=LET(A,ROWS(Sponsor),B, ROWS(School),C,SEQUENCE(A+B),IF(C<= A,INDEX(Sponsor, C,12),INDEX(School,C-A,12)))

B2:
=LET(A,ROWS(Sponsor),B, ROWS(School),C,SEQUENCE(A+B),IF(C<= A,INDEX(Sponsor, C,17),INDEX(School,C-A,17)))

results spill down as far as needed.

To repeat:

Delete expected results from columns A & B.

Do that and it works fine. I may not have deleted far enough down in my posted file.

It was my fault... for not deleting a sufficient number of rows of your older formulae...