VLOOKUP on the if false of another VLOOKUP
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!
Best Answer
-
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
Answers
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!