MATCH, IF, AND Formula Help Needed

[Yosemite]

Hello,

Thank you for reading my question. I am trying to create a formula but can't figure it out. This is what I'm looking for:


IF anything in Column A matches

AND of those matches, If any cell in Column B is equal to 3140, 3140L, 3141, 3141L, 3142, 3142L, 3143, 3143L, 3144, 3144L, 3145, 3145L, 3146, 3146L

THEN the result in Column C should be either 3140, 3140L, 3141, 3141L, etc.


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]51910
[/TD]
[TD]3140
[/TD]
[TD]3140
[/TD]
[/TR]
[TR]
[TD]51910
[/TD]
[TD]4301
[/TD]
[TD]3140
[/TD]
[/TR]
[TR]
[TD]51910
[/TD]
[TD]8043
[/TD]
[TD]3140
[/TD]
[/TR]
[TR]
[TD]78047
[/TD]
[TD]4095
[/TD]
[TD]3141L
[/TD]
[/TR]
[TR]
[TD]78047
[/TD]
[TD]3141L
[/TD]
[TD]3141L
[/TD]
[/TR]
[TR]
[TD]78047
[/TD]
[TD]4252
[/TD]
[TD]3141L
[/TD]
[/TR]
[TR]
[TD]78047
[/TD]
[TD]3141L
[/TD]
[TD]3141L
[/TD]
[/TR]
[TR]
[TD]78330
[/TD]
[TD]4041
[/TD]
[TD]3144L
[/TD]
[/TR]
[TR]
[TD]78330
[/TD]
[TD]3144L
[/TD]
[TD]3144L
[/TD]
[/TR]
</tbody>[/TABLE]

Can anybody help with the correct formula? I'm so lost!


Thank you!

 

[James006]

Hello,

Can you explain what you mean by :

IF anything in Column A matches

...

 

[etaf]

i dont quite follow
how does excel know what value to put into column C you say
Column C should be either 3140, 3140L, 3141, 3141L, etc.
But whats the rule to decide on the value

in the first example
[TABLE="class: cms_table_grid, width: 300"]
<tbody>[TR]
[TD]51910[/TD]
[TD]4301[/TD]
[TD]3140[/TD]
[/TR]
[TR]
[TD]51910[/TD]
[TD]8043[/TD]
[TD]3140
[/TD]
[/TR]
</tbody>[/TABLE]

column B does NOT equal 3140, 3140L, 3141, 3141L, 3142, 3142L, 3143, 3143L, 3144, 3144L, 3145, 3145L, 3146, 3146L
so why is there a result in C

etc - does not help , as i dont know what that may mean to you

 

[Yosemite]

Like in my example, A2, A3 and A4 all have 51910 so that would be the match.
The Formula in column C would result in 3140 for C2, C3, and C4

 

[Yosemite]

Since A2 matches A3 and A4, then the C2, C3 and C4 would all be 3140 because one of the Matched Cells in Column B is 3140.

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]51910
[/TD]
[TD]3140[/TD]
[TD]3140[/TD]
[/TR]
[TR]
[TD]51910
[/TD]
[TD]4301[/TD]
[TD]3140[/TD]
[/TR]
[TR]
[TD]51910
[/TD]
[TD]8043[/TD]
[TD]3140[/TD]
[/TR]
</tbody>[/TABLE]

Sorry, the etc was referencing 3140, 3140L, 3141, 3141L, 3142, 3142L, 3143, 3143L, 3144, 3144L, 3145, 3145L, 3146, 3146L

 

[etaf]

why is column C 3140 and not one of the other choices
3140, 3140L, 3141, 3141L, etc.
which I assume ETC means its only these choices
3140, 3140L, 3141, 3141L, 3142, 3142L, 3143, 3143L, 3144, 3144L, 3145, 3145L, 3146, 3146L

 

[Yosemite]

why is column C 3140 and not one of the other choices
3140, 3140L, 3141, 3141L, etc.
which I assume ETC means its only these choices
3140, 3140L, 3141, 3141L, 3142, 3142L, 3143, 3143L, 3144, 3144L, 3145, 3145L, 3146, 3146L

In my example, Column C is where the Formula should go. The result is 3140 because the three rows in Column A are 51910 and therefore all match.

There will be several random numbers in column B. But if there is a 3140, 3140L, 3141, 3141L, 3142, 3142L, 3143, 3143L, 3144, 3144L, 3145, 3145L, 3146, or 3146L
in column B then I want 3140, 3140L, 3141, 3141L, 3142, 3142L, 3143, 3143L, 3144, 3144L, 3145, 3145L, 3146, or 3146L to go to all the rows in C that have matching
cells in A.

I hope this makes sense. Thank you for your patience with me.

 

[Eric W]

How about:

	A	B	C	D	E	F
	List
8043	3140L
3141L
3141L	3141L	3141L
3141L
3141L	3141L	3142L
3144L
3144L	3144L	3143L
3144L
3145L
3146L

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]51910[/TD]
[TD="align: right"]3140[/TD]
[TD="align: right"]3140[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]51910[/TD]
[TD="align: right"]4301[/TD]
[TD="align: right"]3140[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3140[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]51910[/TD]

[TD="align: right"]3140[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]78047[/TD]
[TD="align: right"]4095[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3141[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]78047[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]78047[/TD]
[TD="align: right"]4252[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3142[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]78047[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]78330[/TD]
[TD="align: right"]4041[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3143[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]78330[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3144[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3145[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3146[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=IFERROR(LOOKUP(2,1/COUNTIFS($A$1:$A$20,A1,$B$1:$B$20,$E$2:$E$15),$E$2:$E$15),B1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I put the list of values you care about in E2:E15. You can hide the list if you want, or put it on another sheet, but doing it this way makes it easier to update if needed.

 

[Yosemite]

Wow!! That is exactly what I needed! Thank you so much!! You just saved me hours of extra work every month. I appreciate the help!

 

[Eric W]

Happy we could help!