Vlookup simple Help

[igorski88]

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))

 

[igorski88]

WOW!!! I am very impressed with the response speed. thank you all for your help.

The second sheet 2 was suppose to be sheet 3.
I ended up using the following formula and it worked with a small problem:

=IFERROR(VLOOKUP(A5,Sheet1!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A4,Sheet2!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A4,Sheet3!A3:E14,5,FALSE),0)

the problem is the sheets actual name is 2 words and I had to combinde the word to one. IE: SHEET 1, SHEET 2, SHEET 3
is there anything i can do to not have to combine the words in the name?

 

[markmzz]

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.]

Greg Truby,

Thank you for your post. The formula isn't ok.

But, we must not forget that the most important thing is solve the user problem and not to know what formula is the most smart.

Thanks again.

Markmzz

 

[markmzz]

Here is the correct formulas:


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Sheet1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Sheet2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Sheet3</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">a</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">a</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">a</td><td style="text-align: center;;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;">a</td><td style="text-align: center;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">b</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">b</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">b</td><td style="text-align: center;;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;">b</td><td style="text-align: center;;">200</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">c</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">c</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: center;;">c</td><td style="text-align: center;;">300</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Greg's</td><td style="text-align: center;;">321</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Biff's</td><td style="text-align: center;;">321</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Mark's 1</td><td style="text-align: center;;">321</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Mark's 2</td><td style="text-align: center;;">321</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Plan1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">(<font color="Green">D2:D4=A2</font>)*(<font color="Green">E2:E4</font>)</font>)+(<font color="Red">(<font color="Green">G2:G4=A3</font>)*(<font color="Green">H2:H4</font>)</font>)+(<font color="Red">(<font color="Green">J2:J4=A4</font>)*(<font color="Green">K2:K4</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">(<font color="Green">D2:D4=A2</font>)*(<font color="Green">E2:E4</font>)</font>)+(<font color="Red">(<font color="Green">G2:G4=A3</font>)*(<font color="Green">H2:H4</font>)</font>)+(<font color="Red">(<font color="Green">J2:J4=A4</font>)*(<font color="Green">K2:K4</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[T. Valko]

WOW!!! I am very impressed with the response speed. thank you all for your help.

The second sheet 2 was suppose to be sheet 3.
I ended up using the following formula and it worked with a small problem:

=IFERROR(VLOOKUP(A5,Sheet1!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A4,Sheet2!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A4,Sheet3!A3:E14,5,FALSE),0)

the problem is the sheets actual name is 2 words and I had to combinde the word to one. IE: SHEET 1, SHEET 2, SHEET 3
is there anything i can do to not have to combine the words in the name?

You can save a few keystrokes by replacing each instance of FALSE with 0.

I don't understand what you mean about this:

the problem is the sheets actual name is 2 words and I had to combinde the word to one. IE: SHEET 1, SHEET 2, SHEET 3
is there anything i can do to not have to combine the words in the name?

Maybe this...

=IFERROR(VLOOKUP(A5,'Sheet 1'!A3:E14,5,0),0)+IFERROR(VLOOKUP(A4,'Sheet 2'!A3:E14,5,0),0)+IFERROR(VLOOKUP(A4,'Sheet 3'!A3:E14,5,0),0)

When a sheet name contains spaces (and other special characters) then you have to enclose the sheet name in single quotes:

'Sheet 1'
'New Hires'
'Table Of Contents'

 

[igorski88]

Thanx the apostrophe's helped. I now have a working formula. becouse i have so many tabs can i make this formula smaller?

=IFERROR(VLOOKUP(A3,'CFF & CAS FFEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'SW&M FFEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FEX I'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FFEX I'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'Patrolling FEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'CSW FFEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FEX II'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FFEX II'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'Eng FFEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FEX III'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,FFEXIII!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,CONVOY!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,MOUT!A3:E12,5,FALSE),0)+IFERROR(VLOOKUP(A3,WAR!A3:E14,5,FALSE),0)



I was thinking possibly something like: IFERROR(VLOOKUP(A3,INDIRECT("'"&INDEX($G$2:$G$5,MATCH(TRUE,COUNTIF(INDIRECT("'"&$G$2:$G$5&"'!A2:e20"),A3)>0,0))&"'!A2:e20"),5,0),0)
but its not working

 

[T. Valko]

Thanx the apostrophe's helped. I now have a working formula. becouse i have so many tabs can i make this formula smaller?

=IFERROR(VLOOKUP(A3,'CFF & CAS FFEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'SW&M FFEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FEX I'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FFEX I'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'Patrolling FEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'CSW FFEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FEX II'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FFEX II'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'Eng FFEX'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,'FEX III'!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,FFEXIII!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,CONVOY!A3:E14,5,FALSE),0)+IFERROR(VLOOKUP(A3,MOUT!A3:E12,5,FALSE),0)+IFERROR(VLOOKUP(A3,WAR!A3:E14,5,FALSE),0)



I was thinking possibly something like: IFERROR(VLOOKUP(A3,INDIRECT("'"&INDEX($G$2:$G$5,MATCH(TRUE,COUNTIF(INDIRECT("'"&$G$2:$G$5&"'!A2:e20"),A3)>0,0))&"'!A2:e20"),5,0),0)
but its not working

If you make a list of the sheet names in a range of cells...

Book1
	B
2	CFF & CAS FFEX
3	SW&M FFEX
4	FEX I
5	FFEX I
6	Patrolling FEX
7	CSW FFEX
8	FEX II
9	FFEX II
10	Eng FFEX
11	FEX III
12	FFEXIII
13	CONVOY
14	MOUT
15	WAR
Sheet1

Then you could use a formula like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&B2:B15&"'!A3:A14"),A3,INDIRECT("'"&B2:B15&"'!E3:E14")))

 

[T. Valko]

the most important thing is solve the user problem and not to know what formula is the most smart.

Your goal should be both!

You don't want people like me pointing out deficiencies in your formulas all the time do you?

 

[Greg Truby]

Just a quick addendum - sometimes folks have a bit of trouble sorting out all of the quotes characters in formulas like this so I'm gonna purdy up Biff's formula just a smidgeon:

=SUMPRODUCT(SUMIF(INDIRECT("'"&B2:B15&"'!A3:A14"),A3,INDIRECT("'"&B2:B15&"'!E3:E14")))

The blue ones are double quotes, the reds single quotes (aka apostrophes).

 

[T. Valko]

Just a quick addendum - sometimes folks have a bit of trouble sorting out all of the quotes characters in formulas like this so I'm gonna purdy up Biff's formula just a smidgeon:

=SUMPRODUCT(SUMIF(INDIRECT("'"&B2:B15&"'!A3:A14"),A3,INDIRECT("'"&B2:B15&"'!E3:E14")))

The blue ones are double quotes, the reds single quotes (aka apostrophes).

Lipstick on a pig?

 

[Greg Truby]

Hey, I couldn't say if it's a beauty queen or a sow; it's your formula. I didn't actually test the thing. I just figured it'd work.