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

Reply ↓  Report •

#1
August 8, 2018 at 15:34:32
✔ Best Answer
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_Names
In 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$4
Click OK

Now, 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 you

And in the future, Excel questions are best asked in the Office Software forum.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  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!

Reply ↓  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$K
Try 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.

MIKE

http://www.skeptic.com/


Reply ↓  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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question