Forum Discussion
iferror and vlookup help
Hi Lezli,
I'm not sure what do you mean under the merging of data. In general your formula (for 3 sheets, not 5) works, it returns the value from the first sheet where the value of D2 is found.
If you clarify your question with small sample based on attached it'll be easier to answer.
I have a spread sheet that I am using to track the managers of office space. The spread sheet is sent out to each group and they fill in their room numbers info. I would like one master so I do not have to search for the information. I watched a tutorial and have put in the formula like the example. However, when I enter the room number it only searches the first sheet. If you look on the summary page in cell G2 it has a "0" when I search for room C313. But in G4 when I have the formula to search only one sheet it pulls "Stacie Smith" which is correct. If I put in Room FF3820 it pulls up "Bin Li". WHich is correct as you can see in cell G5. I am not sure this is the right way to pull the info. I just want to be able to have all of the people residing in the spaces reporting on one sheet. I planned on copying the formula down because the information in column D will always be the same room numbers. I was merely trying to test the formula which is why I was typing in different locations in D2.
Hi Lezli,
That's since you have room number cc313 in all your sheets (or at least in second one, I didn't check all), but have no Lab Manager for it in next sheet. Thus you shall check not only on error (room # wasn't found in the sheet) but as well if Lab Manager for that room in the sheet is blank or not. If error or blank you go on next sheet, if not return value from current one. That could be like
=IF(LEN(IFNA(VLOOKUP(D2,'REG Braddock 5.18'!D2:P6365,8,FALSE),"")), VLOOKUP(D2,'REG Braddock 5.18'!D2:P6365,8,FALSE), IF(LEN(IFNA(VLOOKUP(D2,'BIO Taylor 5.18'!D2:P6365,8,FALSE),"")), VLOOKUP(D2,'BIO Taylor 5.18'!D2:P6365,8,FALSE), IF(LEN(IFNA(VLOOKUP(D2,'BIO Schiferl 4.18'!D2:P6365,8,FALSE),"")), VLOOKUP(D2,'BIO Schiferl 4.18'!D2:P6365,8,FALSE), "not found")))
and attached.
Or, depends on your business logic, that could be "not found" if room is not found and "not assigned" if room exists but Lab Manager is not assigned. In such case formula will be bit different.
You are my hero!
