I am trying to combine multiple data sets with information on organizational members. The unique identifier I am using is email address. Each email address may appear on multiple sheets and may have different data available in different sheets. There are 7 sheets I am working with.

I am using this formula:

=IFERROR(VLOOKUP(A113,sheet1,11,FALSE),IFERROR(VLOOKUP(A113,sheet2,13,FALSE),IFERROR(VLOOKUP(A113,sheet3,13,FALSE),IFERROR(VLOOKUP(A113,sheet4,13,FALSE),IFERROR(VLOOKUP(A113,sheet5,13,FALSE),IFERROR(VLOOKUP(A113,sheet6,13,FALSE)," "))))))

My understanding of this function as it is written is that excel will look in each of these sheets for the email address in row A113 and will return the value in column 11 (in this case, first name) if the email address is found. So it will look in sheet1, then sheet2, then sheet3, then sheet4, then sheet5, then sheet 6.

The problem I have encountered is when there is a blank value in a sheet that the lookup takes that value instead of skipping it. For example, the email address in A113 does exist in sheet1; however, column 11 (first name) is blank for that row. The formula returns "0." But I know that the email address in A113 also exists in sheet4 and DOES have a value for the "first name" column. Since the formula already found the address in sheet1 though it returns that value even though it is blank.

Is there a way to make this formula work how it is and skip any blank cells? Or is there another way to accomplish this?

Thank you!