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

 

[mikerickson]

As to MIKERICKSON, where did the little number in the middle go? The one that tells the formula which column to look at in the other worksheet? it was the one that always corresponded with the Company name.

Both Barry Houdini and I replaced it with a MATCH function.

 

[puppykak]

the match section in my, barry's and mike's suggested formula finds the column number.

again in what way didn't your formula work?

it didn't work in that I put it in the function bar, and it came up with an error and said that the formula was incorrect...

 

[wsjackman]

one suggestion

don't simplify your formulas too much when asking for assistance. Most of us work with the submitted formula. The problem may be part of the complex formula.

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

Re: I have a very specific question about excel, please help!
<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1><!-- / icon and title --><!-- message -->Quote:
<TABLE border=0 cellSpacing=0 cellPadding=6 width="100%"><TBODY><TR><TD style="BORDER-BOTTOM: 1px inset; BORDER-LEFT: 1px inset; BORDER-TOP: 1px inset; BORDER-RIGHT: 1px inset" class=alt2>Originally Posted by wsjackman
the match section in my, barry's and mike's suggested formula finds the column number.

again in what way didn't your formula work?
</TD></TR></TBODY></TABLE>
it didn't work in that I put it in the function bar, and it came up with an error and said that the formula was incorrect...
</TD></TR></TBODY></TABLE>
what was the reported error?

 

[puppykak]

I haven't simplified the formulas, all I did was put each part on a different line so it could be viewed easier. It says "The formula you typed contains an error."

 

[puppykak]

I tried it with the match function and everything. I think the most difficult part of this for me is that when I show the formula here I have to change it so that I keep confidential information out of the formula, such as the actual names of the companies and the cells# are all messed up too..its really hard to keep track of everything...

would it be easier if I created a whole different template document and filled it with Company 1 and Company 2, and so on? then I can take a picture of the screen and post it...Do you think that would be worth it?

 

[puppykak]

I have the pictures to show you , but do you know how to post an image?
​

 

[puppykak]

Ok, I think the main issue with the MATCH formula is that in the outside document, every company has a differnt column, and I want the formula to go to that specfic column when that specific company name is entered...here are some pics to show you what I mean...

this is the main worksheet i am working with. Where is says #NAME? is where I am trying to put the formula.

and this is the outside worksheet where the formula is getting it's information, you can't really see it under the arrows, but those Company are numbered 1-7:

 

[puppykak]

I hope that helps...

 

[wsjackman]

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

 

[puppykak]

don't feel stupid. the equation in there right now in that pic, is not my origional forula, its a messed up version of the MATCH formula you gave me, i don't think it would let me take a picture of the screen because the error box would pop up.