using IF / And formula to find a value within a range

[ErgoTeam]

hello Guys,

The below formula is not working. it works properly when I Do not have "AND" Condition

{=IF($A6=Baseline!$AR$6:$AR$6000,
IF($B6<=Baseline!$AS$6:$AS$6000,
IF(AND(($D6-($D6*30%))>=Baseline!$AU$6:$AU$6000,($D6+($D6*30%))<=Baseline!$AU$6:$AU$6000),
IF(AND(($E6-($E6*30%))>=Baseline!$AV$6:$AV$6000,($E6+($E6*30%))<=Baseline!$AV$6:$AV$6000),
IF(AND(($F6-($F6*30%))>=Baseline!$AW$6:$AW$6000,($F6+($F6*30%))<=Baseline!$AW$6:$AW$6000),
IF(AND(($G6-($G6*30%))>=Baseline!$AX$6:$AX$6000,($G6+($G6*30 <=Baseline!$AX$6:$AX$6000), Baseline!$AX$6:$AX$6000,0),0),0),0),0),0)}


Below is the table from were we get data to compare
Untitled3.png

Below is the Data table - from where we need to get data
Untitled4.png

Thanks in advance

[AliGW]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jason.b75]

Deleted, misread original formula.

edit:-

Oops, should have been an edit to previous post, not a new reply.

[ErgoTeam]

Hi Ali,

The elaborated explanation is below

Formula in Cell K1 should be like, IF A1 = range A:A worksheet data (IF true) then
IF B2 = range B:B worksheet data (IF true) then IF B2 <= range B:B worksheet data (IF true) then
IF D2(10% up or down will do) = range D:D worksheet data (IF true) then
IF E2(10% up or down will do) = range E:E worksheet data (IF true) then
IF F2(10% up or down will do) = range E:E worksheet data (IF true) then
IF G2(30% up or down will do) = range F:F worksheet data (IF true) then
give me new product Type /Page Count/ Corrected Weight and so on

hope this will help you to understand.

[jason.b75]

IF B2 = range B:B worksheet data (IF true) then IF B2 <= range B:B worksheet data (IF true) then

So which is it, = or <= ?

[ErgoTeam]

Formula in Cell K1 should be like,
IF A1 = range A:A worksheet data (IF true) then
IF B2 <= range B:B worksheet data (IF true) then
IF D2(10% up or down will do) = range D:D worksheet data (IF true) then
IF E2(10% up or down will do) = range E:E worksheet data (IF true) then
IF F2(10% up or down will do) = range E:E worksheet data (IF true) then
IF G2(30% up or down will do) = range F:F worksheet data (IF true) then
give me new product Type /Page Count/ Corrected Weight and so on

[ErgoTeam]

The below formula is working fine. but the once that i want to use is giving me error. in this formula it takes same value but i want to take 10% up down also.

=SUM(IF($A12=Baseline!$AR$6:$AR$6000,IF($B12<=Baseline!$AS$6:$AS$6000,IF(IF($M12="",$C12,$M12)=Baseline!$AT$6:$AT$6000,IF(IF($N12="",$D12,$N12)=Baseline!$AU$6:$AU$6000,IF(IF($O12="",$E12,$O12)=Baseline!$AV$6:$AV$6000,IF(IF($P12="",$F12,$P12)=Baseline!$AW$6:$AW$6000,IF(IF($Q12="",$G12,$Q12)=Baseline!$AX$6:$AX$6000,Baseline!$AX$6:$AX$6000,0),0),0),0),0),0),0))

[ErgoTeam]

Hi Ali,

Can you Help to resolved this?

regards,
Xavier Dsouza

[jason.b75]

Deleted, misread original formula.

[Pete_UK]

You can't use AND like that as you are using arrays - you need to do it like this (changes in red):

{=IF($A6=Baseline!$AR$6:$AR$6000,
IF($B6<=Baseline!$AS$6:$AS$6000,
IF((($D6-($D6*30%))>=Baseline!$AU$6:$AU$6000)*(($D6+($D6*30%))<=Baseline!$AU$6:$AU$6000),
IF((($E6-($E6*30%))>=Baseline!$AV$6:$AV$6000)*(($E6+($E6*30%))<=Baseline!$AV$6:$AV$6000),
IF((($F6-($F6*30%))>=Baseline!$AW$6:$AW$6000)*(($F6+($F6*30%))<=Baseline!$AW$6:$AW$6000),
IF((($G6-($G6*30%))>=Baseline!$AX$6:$AX$6000)*(($G6+($G6*30%))<=Baseline!$AX$6:$AX$6000), Baseline!$AX$6:$AX$6000,0),0),0),0),0),0)}

Also, I think there was a bit missing (in blue).

Hope this helps.

Pete

[jason.b75]

You can't use AND like that as you are using arrays

I would disagree, Pete.

That is exactly what I pointed out in my deleted reply, but at a second glance, the way that the array is set up it should work fine. The missing bit that you noticed is most likely the cause of failure.

[jason.b75]

I would disagree, Pete.

That is exactly what I pointed out in my deleted reply, but at a second glance, the way that the array is set up it should work fine. The missing bit that you noticed is most likely the cause of failure.

This comment was based on the fact that the original formula was nothing more than a logical test, as the 'IF' array was not nested into a useful function, the information we had implied that the only purpose of the formula was to check that all of the criteria were true.

Pete's reply, and my original reply, (which I deleted, thinking it was wrong) were both effectively correct based on the limited information that we had at the beginning.

Hopefully now you can see the importance of providing information on your problem that is both adequate and accurate.

[ErgoTeam]

Tried it still not working

[AliGW]

No, sorry.

[jason.b75]

Try this Array Formula in S6

=MIN(IF(Main!A6=Data!$A$6:$A$5623,IF(Main!B6<=Data!$B$6:$B$5623,IF(IFERROR(ABS(Main!D6/Data!$D$6:$D$5623)<=(Main!D6*10%),0),IF(IFERROR(ABS(Main!E6/Data!$E$6:$E$5623)<=(Main!E6*10%),0),IF(IFERROR(ABS(Main!F6/Data!$F$6:$F$5623)<=(Main!F6*10%),0),IF(IFERROR(ABS(Main!G6/Data!$G$6:$G$5623)<=(Main!G6*30%),0),ROW(Data!$A$6:$A$5623))))))))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then enter this formula into K6

=IF(Main!$S6>0,INDEX(Data!A:A,Main!$S6),"")

Fill the formula down to K295, then copy right to column Q.

[ErgoTeam]

This give is the first row where it finds the data i.e 1535 in cell S6

The Value in A1 can be more the 100 time in Data sheet Column A:A, I should get the value which match all my condition
Below:-

Condition 1: After comparing with cell A6, It should compare B6 with Data sheet Column B:B for the same product type ("1 x Way Display" in A6) if that is less then or equal to the value in B6 then it's true. For example, if value in B6 is 4 then any value from 4 to 0 will fall under tolerance level and so on (i.e 4 and less then 4)


Condition 2: Then compare D6 With Data sheet Column D:D with 10% up/down as buffer i.e. if the value in D6 is 100 then any value between 90 to 110 in Column D:D is true if condition 1 also true.

Condition 3: Then compare E6 with Data sheet Column E:E with 10% up/down as buffer i.e. if the value in D6 is 100 then any value between 90 to 110 in Column E:E is true if (condition 1 and 2) also true.

Condition 4: Then compare F6 with Data sheet Column F:F with 10% up/down as buffer i.e. if the value in D6 is 100 then any value between 90 to 110 in Column F:F is true if (condition 1, 2 and 3) also true.

Condition 5: Then compare H6 with Data sheet Column H:H with 30% up/down as buffer i.e. if the value in D6 is 100 then any value between 70 to 130 in Column H:H is true if (condition 1, 2, 3 and 4) also true.

If all condition are true the that row should be copied to K6:Q6

the below formula is working fine when don't use condition, it gives me row which is exact match value from A6:G6 i.e is no UP / Down condition

=SUM(IF($A6=Data!$A$6:$A$6000,IF($B6=Data!$B$6:$B$6000,IF($D6=Data!$D$6:$D$6000,IF($E6=Data!$E$6:$E$6000,IF($F6=Data!$F$6:$F$6000,IF($G6=Data!$G$6:$G$6000,Data!$G$6:$G$6000,0),0),0),0),0),0))

Hope now i have explained it in a better manner

[jason.b75]

The formula in S6 returns the row where all of the criteria match.

The index function then uses that to return the corresponding data.

Your latest explanation contains errors, I have edited the formula to match those conditions and every row returns 0.

H6 contains Job ID, H:H contains Unit price, so the 2 are not comparable.

Having said that, the errors in your explanation are irrelevant, I understand what you need so no need to explain it again!

Are you sure that your formula works correctly? Using SUM to return a matching row is unreliable, If Rows 10 and 20 both match all criteria, then the formula will return row 30, which might not match.

I've found one thing that is wrong with the formula which I need to fix. Also need to make up a more workable test table, 6000 rows of unsorted data is not user friendly when you need to visually compare the results for accuracy.

I have other things to do as well, so it may be a few hours before I get back with a fixed formula.

[ErgoTeam]

Sure Thanks

I need to compare only

1> Product Type
2> Page Count
3> Length
4> Width
5> Depth
6> Qty

and Yes, for Sum that formula is working fine but too get data in the row its not working.

will wait for your reply

Many thanks

[jason.b75]

I've just done a quick change to the formula for S6 to fix the problem that I had noticed, but I don't have time now to check it against the data for accuracy.

=MIN(IF(Main!A6=Data!$A$6:$A$5623,IF(Main!B6<=Data!$B$6:$B$5623,IF(IFERROR(ABS(Main!D6-Data!$D$6:$D$5623)<=(Main!D6*10%),0),IF(IFERROR(ABS(Main!E6-Data!$E$6:$E$5623)<=(Main!E6*10%),0),IF(IFERROR(ABS(Main!F6-Data!$F$6:$F$5623)<=(Main!F6*10%),0),IF(IFERROR(ABS(Main!G6-Data!$G$6:$G$5623)<=(Main!G6*30%),0),ROW(Data!$A$6:$A$5623))))))))

As before, must be array confirmed with Shift Ctrl Enter.

This should hopefully return the correct row where the data that meets all criteria is found. Note that if there are multiple rows that meet the criteria, then the formula will return the first matching row found.

Give it a go, see if it does what you expect. I'll look into it in more detail later.

edit:-

**Formula Changed**

[ErgoTeam]

After using your formula i am getting this output that is not what i want.

if you see in column N6 = 500 but the value in D6 = 600, since value in N6 has to be 10% up/or down to
D6 value it should hence return a value a False and so on

[jason.b75]

That was the original formula from my last reply, have you tried the edited version?

[jason.b75]

As far as I can see, the formula in post #19 (the edited version, not the original that you tried in post #20) works as required.

Noting rows 26 to 30, the Depth appear to exceed the specified tolerance of 10%, this is because the formatting of column F doesn't display decimal values correctly, the cells all show a depth of 1, but if you double click the cell, the true value of 0.5 is shown, which means that the result in column P falls within the tolerance level.

[ErgoTeam]

Thanks. It has been great help. The edited version of the formula is now working.

But I have just notice that if there are many rows then can i get the row value with highest "Unit Price" for example, in main worksheet with reference to row 292, there are 15 rows (approx) matching the tolerance level but it gives value of first row which should not be the case, instead it should give the row value with highest "unit price" in Data worksheet out of the 15 row found. Have attached file for the same.

Just realized this part now after using your formula. So can you add this new part to the formula.

thanks in advance

[jason.b75]

So to confirm that I understand this correctly before making any changes.

The correct answer in S292 would be 1095?

Simple solution, don't change the formula.

Sort the data sheet with descending unit prices, problem solved.

[ErgoTeam]

thanks Jason for your help. much appreciated.

Formula works fine for me now. It was a great Help.

[ErgoTeam]

Sure, Sorting the data sheet with descending unit prices, problem solved.

but now i have noticed that the condition in formula not working from Column B in Main sheet "Page Count", as I told you earlier, if page count is 2 then less then or equal to value will do i.e 2, 1, 0 should be under tolerance level and so on

Row B290 to B295 have value as 1 or 0 but the row which we find have Page value as 2 which should not be the case. it should give 1 or less the 1. (have explained Condition 1 elaborated above if required)

Row B294 have value as 1 but the row which we find have Page value as 2 which should not be the case. it should give 1 or less the 1.

if needed i can place 0 in blank cell

[jason.b75]

I haven't had chance to look into the errors you describe in post #25, but based on your last reply, I guess that you have solved it on your own.

Let me know if that's not the case.

[ErgoTeam]

Yes. solved

[Pete_UK]

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete