Aug 24 2020 07:18 AM
Aug 24 2020 07:18 AM
I am using this formula to reference one sheet to populate another sheet, and it is returning inconsistent results. Can you tell just by looking at the formula if there is something wrong in the syntax or the formula? Thank you.
=XLOOKUP(E7,'Salesforce Monthly Sales Report'!$B$2:$B$183,'Salesforce Monthly Sales Report'!$F$2:$F$183,"NA",-1)
Sheet 1 (report from QuickBooks Online): Contains Customers and Invoices, but I need to assign Sales Reps based on the Customer (from information contained in the other sheet - Salesforce Monthly Sales Report).
Salesforce Monthly Sales Report: Contains information on Sales Reps that are assigned to a certain customer.
Aug 24 2020 07:57 AM
Based on what I see
the formula will search for the value of E7 in the list from B2:B183 then return the corresponding item from list F2:F183
maybe if you can include an example file explaining what is the problem, we might help
Aug 24 2020 08:39 AM
@herzog330 It could be that "-1". So if you are looking up David Stevenson but the list has Dave Stevenson it may pick David Stevens instead. You could also have spaces or other characters throwing the search off. If you use "0" then at least you will require an exact match or reply with a "NA" so you can check on it.
Aug 24 2020 08:57 AM
@mtarler Thank you guys! I am using a zero and getting quite a few "NA's." I believe the issue is with the report that I downloaded from QuickBooks Online. The results they return do not include punctuation. I wonder if there is a decent workaround in the formula for that or if I need to explore how reports are exported in QBO? Thank you guys again for prompt responses!
Aug 24 2020 10:14 AM
@herzog330 Well I'm glad we figured out the original problem
If on the slim chance the 2 sets are close enough except maybe some extra characters on the end or something you could use MATCH()-INDEX() pair with wildcards. for example if names with suffixes like M.D. and Jr. and such are causing the problem, then a formula could be used to strip off those suffixes and add a "*" in the MATCH case. But without a consistent 'rule' (e.g. there is always a "," after the 'main' name and before the suffixes that are causing a problem) it may be hard.
Can you de-identify a number of 'problem' examples and share?