I have a very specific question about excel, please help!

[puppykak]

I am trying to combine the VLOOKUP and the IF functions in a different kind of way...I made this formula, and in theory it should work..buuuuut..in reality it doesn't.

this is my formula...I want it so that if in the cell G2 there is a certain name of a company then the result will be just like if I had just put in the VLOOKUP formula in the formula bar. I need that information to show up in the cell.

I understand also that it only works with 7 companies, and I have tried it with 7 and 6, but it still doesn't work. But if anyone has a solution for that too might be nice. In the end I actually do need to have 8 Companies.

If you notice the number of the company corresponds with the col_index_num part of the VLOOKUP formula. I need each different Company to have their own individual reference back to this outside Excel Worksheet.

( I broke it up so it is easier to look at)

=IF(G2="Company 1",VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,1,FALSE)," ")&
IF(G2="Company 2",VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,2,FALSE)," ")&
IF(G2="Company 3", VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,3,FALSE)," ")&
IF(G2="Company 4", VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,4,FALSE)," ")&
IF(G2="Company 5", VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,5,FALSE)," ")&
IF(G2="Company 6", VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,6,FALSE)," ")&
IF(G2="Company 7", VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,7,FALSE)," ")&
IF(G2="Company 8", VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,8,FALSE)," ")

 

[wsjackman]

name error.

There is nothing obvious in the formula

try
first
the company 2 section if statement by itself
then j
ust the lookup section by itself
then
=b3="Company 2"

also have you tried evaluate formula to try to evaluate where in the formula the error is happening?

 

[puppykak]

we're so close! please don't give up now!

 

[puppykak]

how do you evaluate a formula?

 

[puppykak]

name error.

There is nothing obvious in the formula

try
first
the company 2 section if statement by itself
then j
ust the lookup section by itself
then
=b3="Company 2"

also have you tried evaluate formula to try to evaluate where in the formula the error is happening?

and also, I'm not sure, how you mean to break it up? can you show me please?

 

[wsjackman]

is this project manager 1 2 or 3 you are looking at?

also =match(b5,'[....]Sheet1'!A:A,0)

the name error is confusing
I don't see anything inthe formula

if you enter = and goto the external sheet and select a cell, does it show a similar format to what you have in the formula?

I have been assuming you have th eother workbook open, is this correct?

 

[wsjackman]

how do you evaluate a formula?

in 2003 I think it is in either tools or data - auditing tools evaluate formula
in 2007 in formulas-formula auditing- evaluate formula

to test each section by itself

=IF(G2="Company 2",VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,3,FALSE),"")

=VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,3,FALSE)

=match(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$A$39,FALSE)

=IF(G2="Company 2")

etc.

we need to identify where the name error is coming from

 

[puppykak]

in 2003 I think it is in either tools or data - auditing tools evaluate formula
in 2007 in formulas-formula auditing- evaluate formula

to test each section by itself

=IF(G2="Company 2",VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,3,FALSE),"")

=VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,3,FALSE)

=match(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$A$39,FALSE)

=IF(G2="Company 2")

etc.

we need to identify where the name error is coming from

okay, hold on

 

[puppykak]

okay, hold on

the error is coming from the IF statement I believe.

 

[puppykak]

in 2003 I think it is in either tools or data - auditing tools evaluate formula
in 2007 in formulas-formula auditing- evaluate formula

to test each section by itself

=IF(G2="Company 2",VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,3,FALSE),"")

=VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39,3,FALSE)

=match(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$A$39,FALSE)

=IF(G2="Company 2")

etc.

we need to identify where the name error is coming from

when i evaluated the formula for an error, it made an arrow pointing from the Company Name box to the cell I am entering my formula intp.

 

[puppykak]

is this project manager 1 2 or 3 you are looking at?

also =match(b5,'[....]Sheet1'!A:A,0)

the name error is confusing
I don't see anything inthe formula

if you enter = and goto the external sheet and select a cell, does it show a similar format to what you have in the formula?

I have been assuming you have th eother workbook open, is this correct?

what were you talking about with that other formula?