multiple worksheet, multiple criteria horizontal to vertical lookup; error #VALUE!

Question

I have a multiple worksheet document for which I am pulling data from each sheet and combining it on Sheet1! by employee id no.  For most worksheets I can use a simple vlookup to retrieve the information I need, except the last.

The summary worksheet [wk#1] has data listed horizontally by employee id like this:

       A              B             C              D             E                

1   empid        org code  union/non   medical  dental

2  28000546    wk#2        wk#2        wk#3     wk#3

3  28001169    wk#2        wk#2        wk#3     wk#3

4  28036852    wk#2        wk#2        wk#2     wk#3

The information on wk#1&2 have one row per empid, however the information on wk#3 has multiple rows like this:

         A              B           C             D

1  28000546    medical    PPO       1/1/2011

2  28000546    dental      HMO      1/2/2011

3  28000546    vision      VIS1      1/3/2011

4  28001169    medical    PPO2     1/4/2011

5  28001169    dental      HMO2    1/5/2011

6  28001169    vision      VIS2      1/6/2011

7 28036852     medical    POS      1/7/2011

8 28036852     dental       IND      1/8/2011

9 28036852     vision      VIS3      1/9/2011

To retrieve the medical plan & initial coverage date from wk#3 for emp id  28000546, I put this formula in cell D2 

[ IF(VLOOKUP(A2,Sheet2!$A$1:$D$9,MATCH($D$1,Sheet2!$B$1:$B$9,0),FALSE),VLOOKUP(A2,Sheet2!$A$1:$D$9,3,0)&", "&TEXT(VLOOKUP(A2,Sheet2!$A$1:$D$9,4,0),"MM/DD/YYYY")) ]

I got the correct value of [PPO, 01/01/2011]; [PPO2, 01/04/2011]; & [POS, 01/07/2011] respectively.

However, when I tried the same formula to retrieve dental information for cell E2 [ IF(VLOOKUP(A2,Sheet2!$A$1:$D$9,MATCH($E$1,Sheet2!$B$1:$B$9,0),FALSE),VLOOKUP(A2,Sheet2!$A$1:$D$9,3,0)&", "&TEXT(VLOOKUP(A2,Sheet2!$A$1:$D$9,4,0),"MM/DD/YYYY")) ]  to retrieve dental values I get the error "#VALUE!"

I have checked to make sure all cells have correct format.

Please keep in mind I have simplified the information for example purposes the information on the summary worksheet is coming from 5 different worksheets and there are many more benefit programs. The initial coverage dates are actually 2 different dates, not multiple dates.  I just want to be sure my formula is pulling the correct information prior to applying it to the actual workbook.

Answer 1

Looking at Sheet3 I noted: (1) there will be only one row for a specified empid in A and "dental" in B, and (2) you want the date and this is a numerical values. This means we can use (misuse ?) SUMRPDUCT. Here is my Sheet1 --- I only worked on columns D and E

empid	code	union/non	medical	dental
28000546	wk#2	wk#2	01/01/2011	01/02/2011
28001169	wk#2	wk#2	01/04/2011	01/06/2011
28036852	wk#2	wk#2	01/07/2011	00/01/1900

My formula in D2 is shown below. The $s allow me to copy this across and down. Of course the ranges like A1:A9 will need to be expanded since you have more data. Also I leave it to you wrap the formula within an IF to cover the case then no match is found ( as it stands you will get 1/1/1900 , i.e zero)

=SUMPRODUCT(--(Sheet3!$A$1:$A$9=Sheet1!$A2),--(Sheet3!$B$1:$B$9=Sheet1!D$1),Sheet3!$D$1:$D$9)

best wishes

---

Bernard Liengme, Nova Scotia, Canada http://people.stfx.ca/bliengme/

Answer 2

Hi,

It forces conversion of TRUE to 1 and FALSE to 0.

Answer 3

Hi Ijaz,

what is the meaning of -- in the formula ?

See the detailed explanation of SumProduct and the double unary operator -- at:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

===

Regards,

Norman

Answer 4

what is the meaning of -- in the formula ?