How to make IFERROR and VLOOKUP functions skip blank cells when searching multiple sheets

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!

Hi ,

Update sample workbook without confidential data it will be help full to understand quickly and for fast request .

Punnam

The 7 sheets each have different pieces of data. Some have the same columns, but others have extra. Also, since each sheet may have the same email addresses I would end up with duplicates in one sheet if I combined them all and I wasn't sure how VLOOKUP would handle that. I did combine 3 sheets that had all the same data columns, so there are not as many as I started with. Combining all 7 sheets would be a lot of work and I think would negate the use of the nested vlookup that I thought could solved this problem for me.

There are differing col_index_num in my VLOOKUPS because the column number for certain data points is not always the same for each sheet. Several I did make into the same formatting, but not all of them.

Thanks for your response and for the suggestion on taking out the space at the end.

I have attached a sample workbook and took out all the data except for one that can serve as an example. I changed the email address to AB12.

The sheet "Email Master List" is where I am entering all the VLOOKUP formulas to combine the data from the other 8 sheets. The data piece I'm using for the example is "Region." The formula in B4 of Email Master List is attempting to lookup the region for member AB12 from within several sheets (1,2,3,4,5,6). I excluded some sheets because I knew they don't have any data for Region.

In this example AB12 is found by VLOOKUP in sheet1, but the cell is empty. I'm assuming this is why the result is "0" but I'm not sure. AB12 does exist with data in the region cell in sheet4, sheet5, and sheet6.

I experimented with the formula by taking out the sheet3, sheet2, and sheet1 lookups. When I removed sheet 2 or 3 from the formula the result was still 0. When I removed sheet 1 from the formula the result was "central." This is how I came to the conclusion that the problem is because of the empty cell in sheet 1.

I hope this helps make my problem more clear and I appreciate any help you can offer!

Thanks Speshul for your help. I guess it may not be the best approach I am taking. I think I will rethink this. I will combine all the sheets as you have suggested. I will only combine the columns that are common to all the sheets and then use VLOOKUP to fill in the data that is unique to each sheet.

I now have a new problem though because of the duplicate records among all the sheets. When I combine them I need a way to get rid of duplicate rows while still keeping the data from each instance of a record.

For example:
There are 2 instances of johndoe@email.com
the first row has his address, phone number and zip code
the second row has his region and birthday

How can I delete the duplicate row but still keep all the data on John Doe?

I would do this by hand, but I have over 1500 unique records and many more if you count all the duplicates, so it would be astronomical to do that all by hand.

Does anyone know how to do this or have other suggestions? I wonder if I should start a new thread on this question?