0

To assign the office responsible for a certain country I use a VLOOKUP function. In this way, Office China is responsible for Country China, Office Germany for Country Germany, etc.

enter image description here

There are some cases, dependent on a license, that should be assigned to other Offices, not following the VLOOKUP function.

For example:

  • License6 although in Country China, should be assigned to Office Germany
  • License10 although in Country China, should be assigned to Office India

There are a number of exceptions. How do I build these exceptions into the VLOOKUP function?

Community
  • 1
  • 1
Sam
  • 55
  • 5
  • Use a two-column "vlookup", as explained [here](https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another). – BigBen Oct 20 '22 at 18:08
  • 1
    Also, if you have Excel O365, that opens the door to a whole range of functions that completely simplify how you’d do this. – Max R Oct 21 '22 at 13:31
  • @BigBen : Unfortunately, I don't think it will work here, because the 2 column vlookup doesn't match. I have in one "normal" case: COUNTRY vs OFFICE. In exceptional cases - LICENSE vs OFFICE. – Sam Nov 01 '22 at 20:17
  • @MaxR : Unfortunately, I don't have Office365 – Sam Nov 01 '22 at 20:18
  • 1
    Resolve the exceptions first - have them in their own lookup table. If that lookup is N/A then you run the regular rule. – Mathieu Guindon Nov 01 '22 at 23:52
  • 1
    So something like `="Office " & IFERROR(VLOOKUP(licenseValue,licenseLookup,2,FALSE),countryValue)` – Mathieu Guindon Nov 01 '22 at 23:56

1 Answers1

0

You can use the following in cell D2:

=IF(A2:A100="","",IFERROR(XLOOKUP(B2:B100,TB_Exceptions[License],
TB_Exceptions[Office]), IFERROR(XLOOKUP(C2:C100,
TB_CountryOffice[Country],TB_CountryOffice[Office]), 
"CASE NOT FOUND")))

or using LET function to facilitate maintenance of the formula:

=LET(nameRng, A2:A100, licRng,B2:B100, countryRng, C2:C100,
 IF(nameRng="","",IFERROR(XLOOKUP(licRng,TB_Exceptions[License],
 TB_Exceptions[Office]),IFERROR(XLOOKUP(countryRng,
 TB_CountryOffice[Country],TB_CountryOffice[Office]), 
 "CASE NOT FOUND")))
)

sample excel file

Explanation

We are using a nested XLOOKUP starting first with the most restricted search, i.e. the exceptions, and in case of #N/A then it looks for the regular country lookup table. We consider the CASE NOT FOUND as a last resort in case some lookup value was not defined.

We are using Excel Tables to have dynamic ranges in the lookup tables. For the initial data set we don't use it, because for Excel Tables Spilled array formulas aren't supported, i.e. it returns #SPILL! error.

We define the data input ranges larger than the actual input data set, in case we want to include additional data. We add the initial condition: IF(A2:A100="","",..) to treat the blank lines of the input table.

David Leal
  • 6,373
  • 4
  • 29
  • 56