VLOOKUP, search for values and add them

[kcjaries78]

I am working on a spread sheet that searches for an employee's clock number, pulls the data from a particular week and displays it all on one page. I have 6 different tables of different stats, 4 weeks each and I would like to pull all of this data together. I have successfully created a page that when you plug in an employee's number, all of thier stats apear from all 6 data sets for the last 4 weeks. I do this by using

week 1
=IF(ISNA(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)))

week 2
=IF(ISNA(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)))

week 3
=IF(ISNA(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)))

week 4
=IF(ISNA(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)))

<TABLE style="WIDTH: 346pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=460 border=0><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 44pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: black" width=58 height=17></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=101>Credit Cards</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=104>Customers</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 69pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=92>Total Apps</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=105>Per/1000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl76 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>week 1</TD><TD class=xl77 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/8/2008</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>192</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>15.62</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl76 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>week 2</TD><TD class=xl77 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/15/2008</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>193</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5.18</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>week 3</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/22/2008</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>132</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>week 4</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/29/2008</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>105</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>9.52</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18></TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Totals</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>622</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>5</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>8.04</TD></TR></TBODY></TABLE>

I have to account for data that is missing becasue they may not have worked, and then the devide by zero error. This works for me if I separate all 4 weeks into thier own cell, which is handy for the employee to see.

But I want to create a master employee sheet for the manager that combines 4 weeks into one cell. So vlookup up would collect all 4 values of total cards for the week and add them up to get 622. I might also want to devide by 4 to get the 4 week average in the same cell; but if the value is zero...

Maybe vlookup is the wrong function, but I haven't seen any other examples of what I am looking to have happen.

I have taught myself through till this point and 3 days of creating, I need some help for the final push. I've tried different combos and looked online... any suggestions?

 

[cornflakegirl]

You've given a lot of background, which is great, but I'm not sure what the problem is that you're currently trying to solve. Can you give an example of the result that you want?

 

[kcjaries78]

I want to be able to do the 4 lookups

week 1
=IF(ISNA(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)))

week 2
=IF(ISNA(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)))

week 3
=IF(ISNA(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)))

week 4
=IF(ISNA(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)))

All in one cell instead of separately. Add them together so to speak. Jst to save space so all the dta will fit on one page like a summary.

something like

=IF(ISNA(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE))
+(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE))
+(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE))
+(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)))

but this obviously doesn't work....

 

[Stormseed]

check it out, mate:
http://www.mrexcel.com/forum/search.php?searchid=299535

 

[cornflakegirl]

I think that there are two problems with your formula:

1. You've lost your IF(ISNA bit on sections 2, 3 and 4 - don't know if this is just a typo?
2. The "" in your formula if the person did not work a week. If you replace that with a 0, I think it will work.

 

[kcjaries78]

Stormseed, that link you posted didn't find anything when I clicked on it... anyway you can coppy and paste it?

Cornflakegirl, the "" has to do with the ISNA, if no value is found it will put in the cell what is between the "", wich blank works fine, a zero would say they did work but still got nothing...

And I am not sure what the formula for adding multiple vlookup even looks like. Is there a =sum, can I not use multple IF expressions? Is the order of my formula wrong?? This is my problem, what ever I try comes up with an error... I will try to post the spreadsheet someplace tomorrow so if you wanna download it and play with it you can. I'll also layout the page I want, maybe I can make it all fit and I won't have to combine my formulas, though it would still be cool to be able to do it.

 

[Stormseed]

Stormseed, that link you posted didn't find anything when I clicked on it...

Dear...

That link which I posted is the result of a SEARCH made on this Board for other examples of the issue that you are experiencing. In other words, it is a page that contains links to the topic of multiple VLOOKUPs. You can refer to one of these and try to resolve the issue at your end

anyway you can coppy and paste it?

Yes, you can copy and paste it in the Internet Explorer or any browser you use on your computer.

 

[mvptomlinson]

Does this work for you:

=SUM(IF(ISNA(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)),"",VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)),IF(ISNA(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)),"",VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)),IF(ISNA(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)),"",VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)),IF(ISNA(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)),"",VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)))

 

[Stormseed]

@mvptomlinson

It looks like an ARRAY Formula to me....I guess you forgot to mention how to confirm the formula...? "Ctrl+Shift+Enter" ??

 

[mvptomlinson]

Actually, it's not an array formula, but that doesn't change the fact that the formula fails when the "" is returned from the ISNA in each section when True. Maybe this attempt (again, non-array) which coerces "" to 0 in the formula using the N() function:

=SUM(N(IF(ISNA(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)),"",VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE))),N(IF(ISNA(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)),"",VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE))),N(IF(ISNA(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)),"",VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE))),N(IF(ISNA(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)),"",VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE))))<!-- / message --><!-- edit note -->