Formula Help

[mialauren]

Hi,

I am using the formula below to return data.

=IF(AND($G$1="ALL DC's",$T$1="GRI Increase"),LOOKUP(B76,'For 2023 Pull'!A:A,'For 2023 Pull'!C:C)*-1,IF(AND($G$1="ALL DC's",$T$1<>"GRI Increase"),LOOKUP(B76,'For 2023 Pull'!A:A,'For 2023 Pull'!B:B)*-1,IF(AND($G$1<>"ALL DC's",$T$1="GRI Increase"),LOOKUP(A76,'For 2023 Pull'!F:F,'For 2023 Pull'!H:H)*-1,IF(AND($G$1<>"ALL DC's",$T$1<>"GRI Increase"),LOOKUP('All DCs and by DC'!A76,'For 2023 Pull'!F:F,'For 2023 Pull'!G:G)*-1))))

The issue I am having is data is being populated in some fields in which there is no data on the "For 2023 Pull" tab. And when this happens the data pasted into the cell is exactly the same as the field above. I have tried different varriations of locking cells, highlighting a specific range, copy/pasting over any other foumlas in cells etc. and nothing seems to work. Am I missing something? Any help would be greatly appreciated

 

[jorismoerings]

Hi,
You didn't give us much to go on if you're only providing the formula and no sample data to check however as populating the data is driven by the use of criteria followed the LOOKUP function, it's most probable you'll find the cause for wrong data in the remarks and exceptions of LOOKUP.

These are the important ones taken from the MS support site, (MS Excel Lookup)

1. The values in lookup_vector ('For 2023 Pull'!A:A,) must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
2. If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
3. If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.

As you user info shows are using O365, i would suggest changing the LOOKUP function to:

• XLOOKUP if you're sharing the workbook with all 365 users; or
• VLOOKUP if there's a need for downwards compatibility

 

[mialauren]

Thanks so much - I wasn't aware of the need for ascending order on the lookup function think that's the issue. If not, I will switch to Vlookup. Appreciate it!!