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.

Basically the goal here is for the formula to look up the company name in G2, and then according to what it is, for it to then look at the staff's name in G7 and then with those in mind, go to a completely different outside excel worksheet and fetch that data that matches up with the company name and the staff member's name.

So I figured if I use the VLOOKUP formula, which I had tried and it works and then put it in a IF function, then IF G2 says a particular company name, then it would look up a particular column in the other worksheet to find the corresponding data.

Is this impossible?

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

On Wed, 8 Jul 2009 14:28:03 +0100, puppykak wrote:


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.

Basically the goal here is for the formula to look up the company name
in G2, and then according to what it is, for it to then look at the
staff's name in G7 and then with those in mind, go to a completely
different outside excel worksheet and fetch that data that matches up
with the company name and the staff member's name.

So I figured if I use the VLOOKUP formula, which I had tried and it
works and then put it in a IF function, then IF G2 says a particular
company name, then it would look up a particular column in the other
worksheet to find the corresponding data.

Is this impossible?

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

I haven't debugged this formula, so it may require some minor alterations
depending on the specifics of your actual data setup, but, in general, start
with a "simple" vlookup formula, e.g:

=vlookup(g7,'[Outside Excel
Document.xls]Sheet1'!$A$4:$K$39,Column_Number,FALSE)

To derive your appropriate Column Number, use the MATCH function.

Assuming your company names (which might match the contents of G2) are located
in the first ROW of your table, you could use:

match(g2,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$4,0)

If not, either use an appropriate range reference or set up a range with the
companies listed in the proper order.


So, all together:


=vlookup(g7,'[Outside Excel Document.xls]Sheet1'!$A$4:$K$4,match(g2,'[Outside
Excel Document.xls]Sheet1'!$A$4:$K$39,0),FALSE)
--ron