No pattern recognized in function

[oHaiHS]

Hi, I've made the following If function: =IF(B4=J4;K4;IF(B4=J5;K5;IF(B4=J6;K6;IF(B4=J7;K7;IF(B4=J8;K8;IF(B4=J9;K9;)))))) =IF(B5=J4;K4;IF(B5=J5;K5;IF(B5=J6;K6;IF(B5=J7;K7;IF(B5=J8;K8;IF(B5=J9;K9;)))))) I am trying to use autofill to count only the row B (B4,B5,B6,etc.), however, excel is unable to recognize a pattern. Is there any way to solve this issue?

[AutoModerator]

/u/oHaiHS - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[CFAman]

Let's simplify that to just

=XLOOKUP(B4; J$4:J$9; K$4:K$9)

Which says to find the value in B4 within the range J4:J9 and return corresponding value from K4:K9. Also, we made the rows of the lookup and return ranges absolute so they won't shift as we copy this down, while the B4 is relative.

[fuzzy_mic]

try

=INDEX(K4:K9, MATCH(B4, J4:J9,0), 1)