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

 

[Kaps_mr2]

why can't you have the number e.g 3 stored in G2 rather than "Company 3". This would reduce your formulae to :-

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

thanks

Kaps

 

[mikerickson]

Perhaps something like
=VLOOKUP(G7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$39, MATCH(G2,{"Company 1","Company 2","Company 3",...,"Company 8"},0), FALSE)

 

[barry houdini]

Hello puppykak, welcome to MrExcel, you could use a MATCH formula in place of the column index, e.g.

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

alternatively if you could put the company names as a header in each column then you could replace the "array constant" in the MATCH formula [containing the company names] with just the range reference for those cells

 

[wsjackman]

In what way doesn't it work?

the seven if limit is for embedded ifs
the way you have this set up you should be able to do as many as you want.

what do you get with

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

should it be column 1 or 2?

and if g1 looks like it equals "Company 1"
what do you get with
=g1="Company 1"

I would also set up a list of your companies and use

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

maybe match(g2,company list)+1,

 

[puppykak]

I would do that except Company 3 just refers to a different worksheet all together to get the information I need, I need it to be in a viewer friendly format for those who don't understand the formatting.

I hardly understand this myself.

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.

 

[puppykak]

I don't understand, what do you mean by set up a company list. the thing is I want to try to keep the other worksheet, where this formula is retrieving the information from separate, because this will be a template and copied again and again, and if the original outside worksheet changes, I need all of the templates to follow suit.

 

[puppykak]

I am sorry, I feel really inept! I am brand new at this and I learned all of this just yesterday..

 

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

 

[wsjackman]

the company list would just be a list of the companies in the order of the columns you want to look at. this would make it easier for you in that when you modified a company, all you would need to do is to change one list rather than multiple formulas.