** HELP ** Multiple XLOOKUPS

[sruk89]

Hi @Hans Vogelaar,

You have helped me before with a similar request, so hoping you could help me out with this one too.

The idea is the xlookup is referencing cell b2 and looking in the 3 tabs below, "Sacramento Migrated Exchanges", "2b Planned Migrations" & "Ready For Config - On Hold"

If the value of Cell B2 is found on Sacramento Migrated Exchanges I want the return value to be "2b - Migrated", if its found on "Planned 2b Migrations" it will return "2b Migration - Planned", if its found on Ready for Config - On Hold, it will return "2a Migration"

Planned 2b Migration Tab:

The look-up columns are from B:J

Sacramento Migrated Exchanges Tab:

Ready for Config - On Hold Tab:

Look up column A

[#2]

@sruk89 

=IFS(ISNUMBER(MATCH(B2, 'Sacramento Migrated Exchanges'!A:A, 0)), "2b - Migrated", ISNUMBER(MATCH(B2, 'Planned 2b Migrations'!A:A, 0)), "2b Migration - Planned", ISNUMBER(MATCH(B2, 'Ready for Config - On Hold'!A:A, 0)), "2a Migration", TRUE, "-")

[#3]

Hi Hans,

Slight oversight on my behalf looks like Ready for Config - On Hold Tab also contains the "Planned 2b Migrations" as well so can we change this formula so that if any of the codes in Column A are not found on the 2b Migration list then we can assume that this is a 2a migration?

[#4]

@sruk89 

Please explain more clearly, preferably with an example.

[#5]

@Hans Vogelaar ,

Sure, no problem.

So, on the "Ready for Config - On Hold" Tab it may also contain the same code that we are referencing from Cell B2 on the Planned 2b Migration

Blue Circle = MRHUL (Not on Planned 2b) therefore this would be 2a

Red Circle(s) = CLBER/LNWIN(Which is on the Planned 2b Migrations)

Ready for Config - On Hold Tab:

Planned 2b Migration Tab: (MRHUL) is not on this list

[#6 · Accepted solution]

@sruk89 That shouldn't be a problem since we look for the 2b migrations first. But the formula has to be modified for the planned migrations sheet (my mistake):

=IFS(ISNUMBER(MATCH(B2, 'Sacramento Migrated Exchanges'!A:A, 0)), "2b - Migrated", COUNTIF('Planned 2b Migrations'!B:J, B2), "2b Migration - Planned", ISNUMBER(MATCH(B2, 'Ready for Config - On Hold'!A:A, 0)), "2a Migration", TRUE, "-")

[#7 · Accepted solution]

=IFS(ISNUMBER(MATCH(B2, 'Sacramento Migrated Exchanges'!A:A, 0)), "2b - Migrated", COUNTIF('Planned 2b Migrations'!B:J, B2), "2b Migration - Planned", ISNUMBER(MATCH(B2, 'Ready for Config - On Hold'!A:A, 0)), "2a Migration", TRUE, "-")