Vlookup simple Help

I

igorski88

New Member
Joined
Jul 26, 2011
Messages
43
I am using the following formula to find the data im searching for but not all the sheets have the data its looking for if not I want it to keep adding the rest of the sheets without an error code.

=(VLOOKUP(A5,sheet1!A3:E14,5,FALSE))+(VLOOKUP(A4,sheet2!A3:E14,5,FALSE)+(VLOOKUP(A4,sheet2!A3:E14,5,FALSE))
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Welcome to the board. You do not state which version of Excel you are using. But if you are using Excel 2007 or better, you can simply use the IFERROR() function:

=iferror(VLOOKUP(A5,sheet1!A3:E14,5,FALSE),0)+iferror(VLOOKUP(A4,sheet2!A3:E14,5,FALSE),0)+iferror(VLOOKUP(A4,sheet2!A3:E14,5,FALSE),0)
 
Last edited:
Upvote 0
igorski88 said:
I am using the following formula to find the data im searching for but not all the sheets have the data its looking for if not I want it to keep adding the rest of the sheets without an error code.

=(VLOOKUP(A5,sheet1!A3:E14,5,FALSE))+(VLOOKUP(A4,sheet2!A3:E14,5,FALSE)+(VLOOKUP(A4,sheet2!A3:E14,5,FALSE))
Click to expand...
The last 2 VLOOKUPS are the same?

Use SUMIF instead. Something like this:

=SUMIF(Sheet1!A3:A14,A5,Sheet1!E3:E14)+SUMIF(Sheet2!A3:A14,A5,Sheet1!E3:E14)+SUMIF(Sheet3!A3:A14,A5,Sheet1!E3:E14)
 
Upvote 0
igorski88 said:
I am using the following formula to find the data im searching for but not all the sheets have the data its looking for if not I want it to keep adding the rest of the sheets without an error code.

=(VLOOKUP(A5,sheet1!A3:E14,5,FALSE))+(VLOOKUP(A4,sheet2!A3:E14,5,FALSE)+(VLOOKUP(A4,sheet2!A3:E14,5,FALSE))
Click to expand...

Another formula (array formula - use Ctrl+Shift+Enter and not only Enter):

=SUM(1*(Sheet1!A3:A14=A5)*(Sheet2!A3:A14=A4)*(Sheet2!E3:E14))

Or (normal formula - not array formula):

=SUMPRODUCT(1*(Sheet1!A3:A14=A5)*(Sheet2!A3:A14=A5)*(Sheet2!E3:E14))

Markmzz
 
Last edited:
Upvote 0
markmzz said:
Another formula (array formula - use Ctrl+Shift+Enter and not only Enter):

=SUM(1*(Sheet1!A3:A14=A5)*(Sheet2!A3:A14=A4)*(Sheet2!E3:E14))

Markmzz
Click to expand...
Not sure what that formula is supposed to be doing but that 1* bit isn't doing anything useful.
 
Upvote 0
T. Valko said:
Not sure what that formula is supposed to be doing but that 1* bit isn't doing anything useful.
Click to expand...

Note: this isn't the most smart formula but solve the problem of the user. The most important thing is to solve the user problem.

Markmzz
 
Upvote 0
markmzz said:
Note: this isn't the most smart formula but solve the problem of the user. The most important thing is to solve the user problem.

Markmzz
Click to expand...
How do you know if it solves the OPs problem?

Did you notice in the OPs formula that the last 2 VLOOKUPS are EXACTLY the same? That can't be right.
 
Upvote 0
T. Valko said:
How do you know if it solves the OPs problem?

Did you notice in the OPs formula that the last 2 VLOOKUPS are EXACTLY the same? That can't be right.
Click to expand...

Lets wait for the user.

Note: The most important thing is try to solve the user problem.

Markmzz
 
Upvote 0
Mark, in any event, your formula is incomplete. But even if it were complete (versions "a" of your formula), it would still return incorrect results. You can use the Evaluate Formula tool on the formulas tab to study how the matrix multiplication plays out in this simplified example. [Biff's correct, BTW, the "1*" bit is about as much help here as a Congressman.]

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 88px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #969696; FONT-WEIGHT: bold">Sheet1</TD><TD style="BACKGROUND-COLOR: #969696"></TD><TD></TD><TD style="BACKGROUND-COLOR: #666699; FONT-WEIGHT: bold">Sheet2</TD><TD style="BACKGROUND-COLOR: #666699"></TD><TD></TD><TD style="BACKGROUND-COLOR: #33cccc; FONT-WEIGHT: bold">Sheet3</TD><TD style="BACKGROUND-COLOR: #33cccc"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>a</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ccffcc">a</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">1</TD><TD></TD><TD style="BACKGROUND-COLOR: #ccccff">a</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">10</TD><TD></TD><TD style="BACKGROUND-COLOR: #ccccff">a</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">100</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>b</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ccffcc">b</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">2</TD><TD></TD><TD style="BACKGROUND-COLOR: #ccccff">b</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">20</TD><TD></TD><TD style="BACKGROUND-COLOR: #ccccff">b</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">200</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>c</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ccffcc">c</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">3</TD><TD></TD><TD style="BACKGROUND-COLOR: #ccccff">c</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">30</TD><TD></TD><TD style="BACKGROUND-COLOR: #ccccff">c</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">300</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Greg's</TD><TD style="TEXT-ALIGN: right">321</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Biff's</TD><TD style="TEXT-ALIGN: right">321</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Mark's 1</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Mark's 2</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Mark's 1a</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Mark's 2a</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B6</TD><TD>=IFERROR(VLOOKUP(A2,D2:E4,2,FALSE),0)+IFERROR(VLOOKUP(A3,G2:H4,2,FALSE),0)+IFERROR(VLOOKUP(A4,J2:K4,2,FALSE),0)</TD></TR><TR><TD>B7</TD><TD>=SUMIF(D2:D4,A2,E2:E4)+SUMIF(G2:G4,A3,H2:H4)+SUMIF(J2:J4,A4,K2:K4)</TD></TR><TR><TD>B8</TD><TD>{=SUM(1*(D2:D4=A2)*(G2:G4=A3)*(H2:H4))}</TD></TR><TR><TD>B9</TD><TD>=SUMPRODUCT(1*(D2:D4=A2)*(G2:G4=A3)*(H2:H4))</TD></TR><TR><TD>B10</TD><TD>{=SUM(1*(D2:D4=A2)*(E2:E4)*(G2:G4=A3)*(H2:H4)*(J2:J4=A4)*(K2:K4))}</TD></TR><TR><TD>B11</TD><TD>=SUMPRODUCT(1*(D2:D4=A2)*(E2:E4)*(G2:G4=A3)*(H2:H4)*(J2:J4=A4)*(K2:K4))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

@ Biff - well, we don't know for sure that it wasn't a typo - I mean maybe he did mean to double the results from Sheet2. :-D Quite unlikely - but possible nonetheless.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
office 365 VLOOKUP to return not only the cell data, but also the Cell Comment
Replies
2
Views
52
Joe4
Joe4
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
246
Peter_SSs
Peter_SSs
M
  • Question Question
Vlookup between sheets
Replies
4
Views
100
Alex Blakenburg
A
G
  • Question Question
VBA - Dynamic Vlookup
Replies
3
Views
348
GreatOffender
G
E
  • Question Question
Lookup 2/1 works on one line and not on others
Replies
5
Views
157
Fluff
Fluff

Forum statistics

Threads
1,224,534
Messages
6,179,391
Members
452,909
Latest member
VickiS

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top