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]

this is a work project, and i am leaving work now, but i will be back again tomorrow at 8am. thank you so much for your help! And i hope we can fix this tomorrow.

 

[puppykak]

I got the IF statement to work. This is the formula I used:

=IF(B3="Company 2","yes","no")

and it worked! So from that, I figured that I needed " " around my true and false statements, so I entered:

=
(IF("B3=Company 1",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,2,FALSE)",false“)) &
(IF("B3=Company 2",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,3,FALSE)”,”false“)) &
(IF("B3=Company 3",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,4,FALSE)”,”false“)) &
(IF("B3=Company 4",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,5,FALSE)”,”false“)) &
(IF("B3=Company 5",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,6,FALSE)”,”false“)) &
(IF("B3=Company 6",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,7,FALSE)”,”false“)) &
(IF("B3=Company 7",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,8,FALSE)”,”false“)) &
(IF("B3=Company 8",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,9,FALSE)”,”false“))

and I also entered:

=
(IF(B3="Company 1",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,2,FALSE)",”false“)) &
(IF(B3="Company 2",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,3,FALSE)”,”false“)) &
(IF(B3="Company 3",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,4,FALSE)”,”false“)) &
(IF(B3="Company 4",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,5,FALSE)”,”false“)) &
(IF(B3="Company 5",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,6,FALSE)”,”false“)) &
(IF(B3="Company 6",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,7,FALSE)”,”false“)) &
(IF(B3="Company 7",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,8,FALSE)”,”false“)) &
(IF(B3="Company 8",”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,9,FALSE)”,”false“))


the main difference being the placing of the double quotes around the logical test.

but neither of them work! I don't understand why this isn't working...

 

[puppykak]

and which one would be correct:

=
(IF(B3=Company 1,”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,2,FALSE)",”false“) &
(IF(B3=Company 2,”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,3,FALSE)”,”false“) &
(IF(B3=Company 3,”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,4,FALSE)”,”false“) &
(IF(B3=Company 4,”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,5,FALSE)”,”false“) &
(IF(B3=Company 5,”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,6,FALSE)”,”false“) &
(IF(B3=Company 6,”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,7,FALSE)”,”false“) &
(IF(B3=Company 7,”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,8,FALSE)”,”false“) &
(IF(B3=Company 8,”VLOOKUP(B15,'[Outside Excel Worksheet.xls]Sheet1'!$A$4:$K$39,9,FALSE)”,”false“))

or the once that I had before with the double parenthesis's on the end of each one?

 

[puppykak]

.

 

[wsjackman]

you do not want the double quotes around the vlookup and you do not want to have the false inside the double quotes at the end.

what did you get with the vlookup by itself

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

 

[puppykak]

I figured it out!!!!!!! I GOT IT!

Here it is:

=
IF(B3="Company 1", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,2,FALSE),"")&
IF(B3="Company 2", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,3,FALSE),"")&
IF(B3="Company 3", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,4,FALSE),"")&
IF(B3="Company 4", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,5,FALSE),"")&
IF(B3="Company 5", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,6,FALSE),"")&
IF(B3="Company 6", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,7,FALSE),"")&
IF(B3="Company 7", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,8,FALSE),"")&
IF(B3="Company 8", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,9,FALSE),"")


the issue was the parenthesis before the IF! It was that simple!

hahahahaha! yay! I am so happy. Thank you so much for helping me figure this out!

 

[puppykak]

Oh yeah, and the other issue was that in the other file, the outside worksheet, there was a filter on a vital part of the table and it couldn't be reached by the VLOOKUP with the filter there. So once I got rid of the filter, I was pretty much home free!

 

[puppykak]

okay, I have another problem....

when it says #N/A, becuase I haven't entered a person's name yet, there is another box linked to it. This other cell multiplies the box with the huge long formula by the box next to it. But when it says #N/A, so does this other box. Is there a way to have it so that when the formula box says #N/A, this other box will just appear blank? The main point of this is that at the bottom there is a total, and if there are and #N/A's, then it won't give the total of the numbers that are there. It will just appear as #N/A. So this is the tentative formula I have, but once again...it won't work.

-if(c17="#N/A",0,c17*d17)

I need help again, sorry...

 

[puppykak]

 

[puppykak]

actually...if I could just get the Formula we worked on to come out as 0, when the correct data isn't entered instead of #N/A, that would work too!