If/Vlookup formula with up to 4 different variables to check

dezibluenose

New Member
Joined
Oct 6, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hopefully someone can help Me with this - I'm trying to sort out a file for my job and I can't get any further than I've gotten already. The way to fix this is obviously way beyond my excel knowledge.

I have a report with different batch numbers and claims/notifications. Each batch can have either a valid or invalid claim against it. And to save time we've created a formula that looks at the batch numbers and the claim status and says if all claims are valid or if one is invalid.

As you can see below, this works perfectly if there are only two batch numbers - 3 or more and the results mess up.

If you look at the picture below, cells A11-A13 are one batch number with two valid claims and one invalid claim. The results I expected to see in cells C11-C13 is "One claim valid, other rejected". Instead, the formula checks the first two batch numbers of the three then treats the third one as a new batch. I know the formula I've used only checks 2 rows, but when I include the same checks a third or fourth time, I again get the wrong results as it can be checking one batch number against a different batch number


Now I'm assuming to fix this, I'll need some sort of unique identifier in my formula - something that tells excel that if 3 or 4 batch numbers match, we need to do the same checks on all and not just stop at 2 rows but it's something I don't know how to do.

Any help or advice would be much appreciated.


And honestly, this could all be avoided if my boss just let me separate the batch numbers into different tabs so 2 batches numbers were in one tab, 3 in the other etc etc, but she doesn't want that.


I've included details in cells A2 - A9 to show the formula works perfect if there's only two batch numbers. Also, in the actual file, there are no gaps between the batch numbers, I just separated them here so you could see the results clearer.


Upload 1.JPG


Formula continues down the page as below.

Upload 2.JPG
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
How about like
Excel Formula:
=IF(A2="","",IF(B2="","Empty",IF(COUNTIFS(A:A,A2)=COUNTIFS(A:A,A2,B:B,"valid"),"all ok","some rejected")))
 
Upvote 1
Solution
That works perfect - thanks so much for your help.

Now I just need to sit down and break apart how you did it for my own knowledge. Thanks again.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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