The formula below is intended to perform 2 matches. It works well, except when the 2nd field does not have a match in the Payroll tab. If there's no match, I simply want to to put "0" (zero). Thanks.
=XLOOKUP($A3,Payroll!$A$1:$A$400,(XLOOKUP(J$1,Payroll!$A$1:$AZ$1,Payroll!$A$1:$AZ$400,0)),0)
Last edited by JamesParkerDC; 01-12-2022 at 04:21 PM.
You can just wrap it all in an IFERROR(..,0):
You could also use INDEX/MATCH if you want compatibility with earlier office versions:
Please Login or Register to view this content.
WBDPlease Login or Register to view this content.
Office 365 on Windows 11, looking for ✶ rep!
For a start... the term in J1... Other wage... does not appear in payroll.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
You don't have a column called Other Wage on the Payroll sheet & you have told xlookup to return 0 if not found.
Yes...that is actually what I am trying to accomplish. My payroll software only exports columns that have non-Zero numbers. We had no "other wages" this run, so it left it out. I'm trying to get my helper sheet to simply put "0" if there is no data to be found. Instead, I get a #VALUE!, hence the problem. I could use the #IFERROR wrap as WBD proposed above if there is no other solution.Originally Posted by Fluff13
In that case tryFormula:
Please Login or Register to view this content.
Thank you all for your helpful suggestions. GREAT seeing a community like this work to help each other out. I went with WBD's IFERROR wrap. I also used his Index/Match instead of XLookup. It's a bit easier for me to look at. Of course, it worked!!! :D
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks