VLOOKUP
 

I am trying to build a worksheet with 50 tabs and a summary page. Each
contain lists of data. I want the summary page to look up and match the data
on each sheet using employee names. I have been using the VLOOKUP formula,
but am having troubles:

I have 20 or so rows of data on the summary page. However, not every name
appears on every worksheet. So if the name is not there, it returns #VALUE!,
which makes it so my summing formula does not work. So I have also been
trying to use an IF formula.

Additionally, I can get one first name to work so long as it is also the
first name on the other tab, but name 2 does not work - even when it is also
name 2 on the other tabs.

Here is what my VLOOKUP formula looks like:

VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)

And my IF statement is this:

=IF(VLOOKUP($A$7:$A$33,'Event
1'!$A$17:$G$67,7,FALSE)0.01,VLOOKUP($A$7:$A$33,'E vent
1'!$A$17:$G$17,7,FALSE),0)

The VLOOKUP works (with the constraints above) but if the value is False, it
does not return 0, it retruns #VALUE!

VLOOKUP
 

Try the iserror function, if it returns an error insert "" else vlookup
=IF(ISERROR(VLOOKUP($A$7:$A$33,'Event
1'!$A$17:$G$67,7,FALSE)),"",VLOOKUP($A$7:$A$33,'Ev ent 1'!$A$17:$G$67,7,FALSE))
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"a" wrote:

I am trying to build a worksheet with 50 tabs and a summary page. Each
contain lists of data. I want the summary page to look up and match the data
on each sheet using employee names. I have been using the VLOOKUP formula,
but am having troubles:

I have 20 or so rows of data on the summary page. However, not every name
appears on every worksheet. So if the name is not there, it returns #VALUE!,
which makes it so my summing formula does not work. So I have also been
trying to use an IF formula.

Additionally, I can get one first name to work so long as it is also the
first name on the other tab, but name 2 does not work - even when it is also
name 2 on the other tabs.

Here is what my VLOOKUP formula looks like:

VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)

And my IF statement is this:

=IF(VLOOKUP($A$7:$A$33,'Event
1'!$A$17:$G$67,7,FALSE)0.01,VLOOKUP($A$7:$A$33,'E vent
1'!$A$17:$G$17,7,FALSE),0)

The VLOOKUP works (with the constraints above) but if the value is False, it
does not return 0, it retruns #VALUE!

VLOOKUP
 

I have tried that and it still reads #VALUE!

"John Bundy" wrote:

Try the iserror function, if it returns an error insert "" else vlookup
=IF(ISERROR(VLOOKUP($A$7:$A$33,'Event
1'!$A$17:$G$67,7,FALSE)),"",VLOOKUP($A$7:$A$33,'Ev ent 1'!$A$17:$G$67,7,FALSE))
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"a" wrote:

I am trying to build a worksheet with 50 tabs and a summary page. Each
contain lists of data. I want the summary page to look up and match the data
on each sheet using employee names. I have been using the VLOOKUP formula,
but am having troubles:

I have 20 or so rows of data on the summary page. However, not every name
appears on every worksheet. So if the name is not there, it returns #VALUE!,
which makes it so my summing formula does not work. So I have also been
trying to use an IF formula.

Additionally, I can get one first name to work so long as it is also the
first name on the other tab, but name 2 does not work - even when it is also
name 2 on the other tabs.

Here is what my VLOOKUP formula looks like:

VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)

And my IF statement is this:

=IF(VLOOKUP($A$7:$A$33,'Event
1'!$A$17:$G$67,7,FALSE)0.01,VLOOKUP($A$7:$A$33,'E vent
1'!$A$17:$G$17,7,FALSE),0)

The VLOOKUP works (with the constraints above) but if the value is False, it
does not return 0, it retruns #VALUE!

VLOOKUP
 

Try entering as an array formula which it is.

CTRL + SHIFT + ENTER


Gord Dibben MS Excel MVP

On Mon, 5 Nov 2007 11:37:03 -0800, a wrote:

I have tried that and it still reads #VALUE!

"John Bundy" wrote:

Try the iserror function, if it returns an error insert "" else vlookup
=IF(ISERROR(VLOOKUP($A$7:$A$33,'Event
1'!$A$17:$G$67,7,FALSE)),"",VLOOKUP($A$7:$A$33,'Ev ent 1'!$A$17:$G$67,7,FALSE))
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"a" wrote:

I am trying to build a worksheet with 50 tabs and a summary page. Each
contain lists of data. I want the summary page to look up and match the data
on each sheet using employee names. I have been using the VLOOKUP formula,
but am having troubles:

I have 20 or so rows of data on the summary page. However, not every name
appears on every worksheet. So if the name is not there, it returns #VALUE!,
which makes it so my summing formula does not work. So I have also been
trying to use an IF formula.

Additionally, I can get one first name to work so long as it is also the
first name on the other tab, but name 2 does not work - even when it is also
name 2 on the other tabs.

Here is what my VLOOKUP formula looks like:

VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)

And my IF statement is this:

=IF(VLOOKUP($A$7:$A$33,'Event
1'!$A$17:$G$67,7,FALSE)0.01,VLOOKUP($A$7:$A$33,'E vent
1'!$A$17:$G$17,7,FALSE),0)

The VLOOKUP works (with the constraints above) but if the value is False, it
does not return 0, it retruns #VALUE!