# Solved Excel Vlookup over multiple sheets and if

August 8, 2018 at 10:25:47
Specs: Windows 10
 I need a formula that will tell me if a customer has participated in an event at all during the year. The data is divided into 4 quarters (each quarter in it's own spreadsheet). There is a "Y" indicating if the customer participated. I need to know if they participated in any across the 4 sheets (if yes, "Y", if no, blank). I need this formula to fill down a column and correspond with the customer ID. Also, it needs to be like a vlookup since the customer IDs across the 4 sheets may not be in the same cells in the column. Although all the spreadsheets have the same headers.Column A is the Customer ID and Column K is participation. This is true for all 4 Qtrs and the final year-end sheet where I want to put my formula.message edited by Lisa99

See More: Excel Vlookup over multiple sheets and if

#1
August 8, 2018 at 15:34:32
 Here is a SUMPRODUCT() & COUNTIFS() solution.First you need to define a Named Range for your individual woeksheets, so someplace out of the way, on your Summary Sheet,like column AZ for example, make a list of your worksheets:``` AZ 1) Sheet1 2) Sheet2 3) Sheet3 4) Sheet4 ```Next, on the ribbon select the Formula Tab,select Define Name, A popup window should appear In the Name Box enter a range name, I have used: My_Sheet_NamesIn the Scope box, it should read Workbook, so just leave it as that.In the Comment box you can write anything you like to remined you what this named range is for.In the Refers To box enter: =Summary!\$AZ\$1:\$AZ\$4Click OKNow, with your ID's in column A,``` A 1) User ID 2) 123 3) 124 4) 125 5) 126 6) 127 7) 128 8) 129 9) 130 ```In cell B2 enter the formula:=SUMPRODUCT(COUNTIFS(INDIRECT("'"&My_Sheet_Names&"'!\$A:\$A"),A2,INDIRECT("'"&My_Sheet_Names&"'!\$K:\$K"),"y"))Now simply drag the formula down as many rows as needed.The formula makes the assumption that column A, in all the worksheets, is the ID column and that column K, in all the worksheets, is the Y/N column.See how that works for youAnd in the future, Excel questions are best asked in the Office Software forum.MIKEmessage edited by mmcconaghy

Report •

#2
August 9, 2018 at 05:41:31
 Thank You!!! It worked. It's a little slow, but I don't need it to be lightening fast since it's only a one time of year calculation. Really appreciate your help!

Report •

#3
August 9, 2018 at 09:47:07
 It's a little slow,How many ID's are you going thru?Two suggestions that might speed things up a bit.In the formula I used the entire column, IE \$A:\$A and \$K\$KTry changing those to the actual range of your data, so if you have 500 rows of data with one header row,the formula would be something like:=SUMPRODUCT(COUNTIFS(INDIRECT("'"&My_Sheet_Names&"'!\$A2:\$A500"),A2,INDIRECT("'"&My_Sheet_Names&"'!\$K2:\$K500"),"y"))Also, Sort your data, if it isn't already.See if that helps.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
August 9, 2018 at 10:22:08
 Or, if the range might change size, create a Dynamic Named Range so that the formula will always refer to exact size of the list.

Report •