MATCH, IF, AND Formula Help Needed

Y

Yosemite

New Member
Joined
Nov 16, 2018
Messages
5
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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

 
Upvote 0
etaf said:
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
Click to expand...

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.
 
Upvote 0
How about:


ABCDEF
List
80433140L
3141L
3141L3141L3141L
3141L
3141L3141L3142L
3144L
3144L3144L3143L
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
formula to extract actual average based on multiple criteria and ignore blank cell
Replies
1
Views
790
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top