Formula retuning inconsistent results

Copper Contributor

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.

5 Replies

@herzog330 

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

@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.

@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!

we need an example file to work with

@herzog330  Well I'm glad we figured out the original problem :)  Now for QBO, I have some experience with QB (not online) and recall it being very finnicky.  In my case we had issues because accounting entering the names in QB entered them slightly different than the product services group entered the names in their db. Our only solution was to create a 'conversion/lookup' table. 

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?