Match & lookup confusion

Hello - I am trying to uses MATCH and LOOKUP I believe. I have been trying off and on for two days now and I cannot seem to figure our what to do.

I enter numbers in cells D5 - G38 I want the formula to look at the reference cells in W4 - X35.

When I type 22 in cell D5 I want the formula to LOOKUP 22 in the reference cells. Once it finds a MATCH with 22in cell W18 I want it to look at cell X18 and see the 4 there.

Then I want it to look for a MATCHing 4 in cells J3 - U3 and see that J3 and N3 and put a COUNT of 1 in cells J7 and M7.

I want the same to happen to the other numbers that I have in cells E5 - G5.

Thanks for your help.

I am using Microsoft 365 now :-)

Yes that did work, but I would have NEVER come up with that formula. That was some real Einstein / Elon Musk type of formula crafting. I am going to have to look up the operators that you put in there. Also, I have never heard of or seen a formula that "spills to fill the row"!!! That was wild.

I do have a question for you Ali. How do I use that formula all the way down the column J7 - J40? When I dragged the formula down it filled all of the rows below it with the same information. When I entered numbers in the row D - G it did not recognize them.

Bebo can I ask for your help one more time with this spreadsheet. I really liked how you fixed my issue last time. I liked it so much that I expanded the spreadsheet and and added more columns. I also changed the numbers in cells J8 - BE8. I tried to edit your last formula, but I failed at doing it as you can see in the spreadsheet. If you can help me with this again I would greatly appreciate your help. Thank you Bebo.

On my Corners Expanded (2) tab the COUNT is not working correctly. Someone helped me with this formula, but I cannot figure out how to fix the issue.

My problem is that if I enter the same number in cells D4 - G4 it only shows a count of 1 in cell L4.

The COUNT works correctly when I do 4, 13, 22 and 31 in D5 - G5. It just will not work if the same number is entered on the same row.

Can someone please give me a hand with this formula? Thanks for your help.

WOWOWOWOW AliG!!!! Just WOWOWOW!!! That looks more like a computer program than an excel formula. That is some extremely impressive excel calculations. I did not even know that excel can do lambda. I am just blown away at this solution!!! Thank you very very much Ali!!!

I do have just one last question for you Ali. Is it possible to enter Zero as one of those numbers? I noticed when I entered a Zero on the line it killed all 4 entries for that row. Is a Zero needed in the reference cells and a Column just for the Zero also?

Sorry Ali. i was not thinking about the zero. In most of my spreadsheets when I enter a zero it does nothing to the rest of my spreadsheet. With this one if I enter zero it kills all data entry on that line. I have NEVER seen anything like that so I would not know to comment about that. Seeing that happen where it forbids results is something that I have never ever seen before.

My simple solution will be to just leave that cell blank so it records, but also so it only records 3 inputs instead of 4 since the zero is not part of my count. Thank you.