If And Formula with Index not Working

MHExcel

New Member
Joined
Feb 15, 2018
Messages
2
I am wanting to compare a new store to existing store performance. Any stores that meet the criteria such as same location, sales within 5%, GP within 10% and size within 10%.

The below formula loads in all store names not just the ones that meet the criteria can someone tell me what I have done wrong in the formula.

[TABLE="width: 161"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 1627"]
<colgroup><col></colgroup><tbody>[TR]
[TD]IFERROR(INDEX(Table1[Store Name],(IF(AND($D$3 - Table1[Retail m2]) <= 10%, IF(AND($D$3 - Table1[Retail m2]) >= 10%, IF(AND($B$3-Table1[Sales])<=5%,IF(AND($B$3-Table1[Sales])>=5%, IF(Table1[Location]= $F$3, IF(AND($E$3 - Table1[Qty Sold Per Day]) <= 10%, IF(AND($F$3 - Table1[Qty Sold Per Day]) >= 10%,))))))))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
you have definitely got some of your brackets wrong, just looking at the first bit your "AND" statement is:
AND($D$3 - Table1[Retail m2])
you then have "<=10%" sitting outside the "And" brackets, so that isn't going to work. You have this elsewhere as well and probably other errors. The way I debug long statements like this is to use lots of cells and break each part of the statement down to single statement so that I can get the syntax correct for that little bit. I then cut and paste the formula in to notepad (or any othe text editor) where it is easy to combine them together.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,876
Members
452,363
Latest member
merico17

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