I need to do a lookup on one table, and if the value is not there do a search on another table. I thought I couch do this with an XLOOKUP, but that does not seem to work. My formula is:
=IF(XLOOKUP([@[Applicable QMS Entity]],tbl_UserGroups[QMS Entity],tbl_UserGroups[Translation User Group],"-",0,1),XLOOKUP([@[Applicable QMS Entity]],tbl_Translation[QMS Entity],tbl_Translation[Translation User Group],"-",0,1),"-")
I even tried it in VLOOKUP format, and this did not work either:
=IF(VLOOKUP([@[Applicable QMS Entity]],tbl_UserGroups,2,FALSE),VLOOKUP([@[Applicable QMS Entity]],tbl_Translation,2,FALSE),"-")
My XLOOKUP and my VLOOKUP formula resulted in a #VALUE Error. I don't understand what is wrong, or if there is a better way to get this information. The thing is I cannot combine these 2 tables. And not all of them appear on both. But if they DO appear on the first table the value that matches must come from there, otherwise the formula should look at the second table to find a match.
Impact Sheet
location of formula
Applicable QMS Entity (field)
codes sheet
location of tbl_UserGroups (Table 1)
QMS Entity (1st field)
Translation User Group (2nd field)
Translations Sheet
tbl_Translation (Table 2)
QMS Entity (1st field)
Translation User Group (2nd field)
Update: I solved on my own! (wooohooo!!!) For anyone interested in the solution so they can apply to their own workbooks, based on all my criteria above, my formula was:
=IFERROR(VLOOKUP([@[Applicable QMS Entity]],tbl_UserGroups,2,FALSE),VLOOKUP([@[Applicable QMS Entity]],tbl_Translation,2,FALSE))
Lookup Value: [@[Applicable QMS Entity]]
Table1: tbl_UserGroups
Column Needed: 2
Table2: tbl_Translation
Column Needed: 2
Just in case you are still interested in a solution that uses XLOOKUP, perhaps the example in the attached file is useful.
You can enter the second XLOOKUP in the [if_not_found] argument of the first XLOOKUP.
@Riny van Eekelen,
I was so close on my XLOOKUP. Thanks for providing the correct solution in the XLOOKUP function. I prefer to use XLOOKUP to VLOOKUP for so many reasons. I will be adding that to my Excel Cheat Sheet Library (notes section of Outlook (first line post title, 3rd line, URL of site I found info at, and 5th line begins formula and criteria, like I laid mine out). This is a great way to have those formulas at your fingertips, when you don't have it memorized.
Thanks again for all your help!!!!!
