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.

A	B	C
51910	3140	3140
51910	4301	3140
51910	8043	3140
78047	4095	3141L
78047	3141L	3141L
78047	4252	3141L
78047	3141L	3141L
78330	4041	3144L
78330	3144L	3144L

<tbody>
</tbody>

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

51910	4301	3140
51910	8043	3140

<tbody>
</tbody>

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.

A	B	C
51910	3140	3140
51910	4301	3140
51910	8043	3140

<tbody>
</tbody>

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
1	51910	3140	3140		List
2	51910	4301	3140		3140
3	51910	8043	3140		3140L
4	78047	4095	3141L		3141
5	78047	3141L	3141L		3141L
6	78047	4252	3141L		3142
7	78047	3141L	3141L		3142L
8	78330	4041	3144L		3143
9	78330	3144L	3144L		3143L
10					3144
11					3144L
12					3145
13					3145L
14					3146
15					3146L

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula C1 =IFERROR(LOOKUP(2,1/COUNTIFS($A$1:$A$20,A1,$B$1:$B$20,$E$2:$E$15),$E$2:$E$15),B1) <tbody> </tbody>

<tbody>
</tbody>

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!