VLOOKUP on the if false of another VLOOKUP

Walter Kerfoot

I have a worksheet that I want to reference 5 reference sheets that contain staff members. Because of the large scale of staff members. We split them up by region. I want to create a column formula that will look up a "District ID" and return the "User" If they exist in one of the 5 reference sheets. This is my current formula:

=IF([District ID]@row = [District ID]@row, VLOOKUP([District ID]@row, {West}, 2, false), VLOOKUP([District ID]@row, {District}, 2, false), VLOOKUP([District ID]@row, {East}, 2, false), VLOOKUP([District ID]@row, {South}, 2, false), VLOOKUP([District ID]@row, {North}, 2, false))

When I use this formula it gives me a #NO MATCH . if I house the value I am looking for in District or any others later down in the formula. So I think its only preforming the first vlookup and not doing the 2nd, 3rd, 4th or 5th one if it fails to find that match in the first.

In the past I switched away from VLOOKUP and had used an INDEX SUMIF HAS to pull specific contact info if it met criteria. I am unsure if I can make this one work for this use case. This is what I would use:

=INDEX([Reference sheet column I want to look in}, SUMIFS({Row ID on Reference sheet}, {Refence sheet column Criteria}, HAS(@cell, "Reference Criteria"@row)), 0)

Any advice or Formula rewrites are appreciated!

Genevieve P.

Hi @Walter Kerfoot

Instead of using IF, try using IFERROR. When one of the VLOOKUPs doesn't find a match, it will return a #NoMatch error, so we can say IF there's an ERROR, move on to the next VLOOKUp... and if that one errors (another IFERROR), move on to the next VLOOKUP, etc.

=IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP([District ID]@row, {West}, 2, false), VLOOKUP([District ID]@row, {District}, 2, false)), VLOOKUP([District ID]@row, {East}, 2, false)), VLOOKUP([District ID]@row, {South}, 2, false)), VLOOKUP([District ID]@row, {North}, 2, false))

See: IFERROR Function

Cheers,

Genevieve

Genevieve P.

Hi @Walter Kerfoot

Instead of using IF, try using IFERROR. When one of the VLOOKUPs doesn't find a match, it will return a #NoMatch error, so we can say IF there's an ERROR, move on to the next VLOOKUp... and if that one errors (another IFERROR), move on to the next VLOOKUP, etc.

=IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP([District ID]@row, {West}, 2, false), VLOOKUP([District ID]@row, {District}, 2, false)), VLOOKUP([District ID]@row, {East}, 2, false)), VLOOKUP([District ID]@row, {South}, 2, false)), VLOOKUP([District ID]@row, {North}, 2, false))

See: IFERROR Function

Cheers,

Genevieve