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!
