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

 

[puppykak]

i feeel stupid

in your original formula replace False with "" in each case


the "name" error indicates you have something in the formula which excel does not recognise

you could use FALSE() but then you would get "false" as the if-false result

False by itself is not a function, False() is

this is a pic of when my original formula was in there. it wouldn't even let me get out of the function bar...

 

[wsjackman]

what happens when you replace the false with ""?

I was wrong you can just use false, but it would display "false" as the result

also have you tried adding one to the column indicator?

have you tried any if statement by itself?

 

[puppykak]

i have added one to the column indicator...thats whats shown in the picture

i tried what you said...i think:

 

[puppykak]

Oops, I meant:

 

[puppykak]

what happens when you replace the false with ""?

I was wrong you can just use false, but it would display "false" as the result

also have you tried adding one to the column indicator?

have you tried any if statement by itself?

i just tried and IF statement by itself, and I can't even get that to work.

 

[wsjackman]

I need to learn to not misread formulas

I put the false in the wrong spot in my reading of the formula

I would change the first section from

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

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

it looks as though you may be getting close to what you want?

was the 39.9 what you wanted to see?


<TABLE id=post1992077 class=tborder border=0 cellSpacing=0 cellPadding=6 width="100%" align=center><TBODY><TR vAlign=top><TD style="BORDER-BOTTOM: #ffffff 0px solid; BORDER-LEFT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-RIGHT: #ffffff 1px solid" class=alt2 width=175></TD><TD style="BORDER-RIGHT: #ffffff 1px solid" id=td_post_1992077 class=alt1><!-- report button --><!-- / report button --><!-- icon and title --></TD></TR></TBODY></TABLE>

 

[wsjackman]

the formula in this picture is not the same as your original submital. what happens when you enter the submittal fomula (Corrected for column).

The formula in the screen shot has many errors in it.

Oops, I meant:

 

[puppykak]

I didn't get that, it won't work for me:

 

[puppykak]

the submittal formula has g2 and g7 in it instead of b3 and b15, those are the only differences I see. i had to switch because back then, i was working off of a different table. what errors are there?

 

[puppykak]

Okay, I found some of the errors in my last one hopefully, aand its not giving me an error anymore, just a #NAME?

so this is what i've got: