Nested Vlookup Functions and Formulas in Excel



Good day,
I have filled the following formula in many cells of a spreadsheet.
=IF(ISERROR(VLOOKUP(D75,'Sheet1'!$D$3:$D$5442,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75,'Sheet1'!$C$3:$C$54 42,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75&"  "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE)),VLOOKUP(C75&"  "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE),VLOOKUP(C75&"  "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE))))
I am wondering if I have nested too many functions for this to work properly. In the case where one of the first two if statements are true (an error would be produced), I am receiving a "0" as specified. However, in the case where the statement VLOOKUP(C75&"  "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE) results in an error then I should have the return of VLOOKUP(C75&"  "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE), but am getting a #REF! error instead.
Any thoughts?
Thank you in advance,
N
I have filled the following formula in many cells of a spreadsheet.
=IF(ISERROR(VLOOKUP(D75,'Sheet1'!$D$3:$D$5442,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75,'Sheet1'!$C$3:$C$54 42,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75&"  "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE)),VLOOKUP(C75&"  "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE),VLOOKUP(C75&"  "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE))))
I am wondering if I have nested too many functions for this to work properly. In the case where one of the first two if statements are true (an error would be produced), I am receiving a "0" as specified. However, in the case where the statement VLOOKUP(C75&"  "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE) results in an error then I should have the return of VLOOKUP(C75&"  "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE), but am getting a #REF! error instead.
Any thoughts?
Thank you in advance,
N
Hi guys, hopefully you can shed some light onto this small problem I am having.
The current formula I'm trying to use is:
=IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(PASS,""))+IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(F AIL,""))
This isn't producing the desired results.
What I am trying to do in words is this:
IF... in this array it equals A4 then look at coloum 6 and then Countif col 6 says Pass and then add another if it says Fail
I think I've gotten the syntax mixed up...can anyone guide me?
The current formula I'm trying to use is:
=IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(PASS,""))+IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(F AIL,""))
This isn't producing the desired results.
What I am trying to do in words is this:
IF... in this array it equals A4 then look at coloum 6 and then Countif col 6 says Pass and then add another if it says Fail
I think I've gotten the syntax mixed up...can anyone guide me?
Hey all,
I am new to excel and this forum, so sorry for the clumsiness.
So, I am trying to use Vlookup to 'grab' a date from one sheet and place it in another. It is important that if the value cannot be found then the cell is left blank (no #N/A).
I built the following and it works well:
IF(ISERROR(VLOOKUP(B17,Sheet1!A32:M94,11,FALSE)),"",VLOOKUP(B17,Sheet1!A32:M94,11,FALSE))
Next, I want the Vlookup to deal with three IF functions. So, if the first cell is blank it checks the previous, and if that cell is blank then it checks the previous and finally if that cell is blank does not return any value (including #N/A).
I tried this:
=IF(ISBLANK(VLOOKUP(B17,Sheet1!A32:M94,13,FALSE)),IF(ISBLANK(VLOOKUP(B17,Sheet1!A32:M94,12,FALSE)),I F(ISERROR(VLOOKUP(B17,Sheet1!A32:M94,11,FALSE)),"",VLOOKUP(B17,Sheet1!A32:M94,11,FALSE)),VLOOKUP(B17 ,Sheet1!A32:M94,12,FALSE)),VLOOKUP(B17,Sheet1!A32:M94,13,FALSE))
This does everything all the way to if the last cell is blank it places #N/A instead of leaving the target cell blank.
I have attached my sheets, if that helps.
Any thoughts?
If I need to clarify my question just let me know.
Thanks!
I am new to excel and this forum, so sorry for the clumsiness.
So, I am trying to use Vlookup to 'grab' a date from one sheet and place it in another. It is important that if the value cannot be found then the cell is left blank (no #N/A).
I built the following and it works well:
IF(ISERROR(VLOOKUP(B17,Sheet1!A32:M94,11,FALSE)),"",VLOOKUP(B17,Sheet1!A32:M94,11,FALSE))
Next, I want the Vlookup to deal with three IF functions. So, if the first cell is blank it checks the previous, and if that cell is blank then it checks the previous and finally if that cell is blank does not return any value (including #N/A).
I tried this:
=IF(ISBLANK(VLOOKUP(B17,Sheet1!A32:M94,13,FALSE)),IF(ISBLANK(VLOOKUP(B17,Sheet1!A32:M94,12,FALSE)),I F(ISERROR(VLOOKUP(B17,Sheet1!A32:M94,11,FALSE)),"",VLOOKUP(B17,Sheet1!A32:M94,11,FALSE)),VLOOKUP(B17 ,Sheet1!A32:M94,12,FALSE)),VLOOKUP(B17,Sheet1!A32:M94,13,FALSE))
This does everything all the way to if the last cell is blank it places #N/A instead of leaving the target cell blank.
I have attached my sheets, if that helps.
Any thoughts?
If I need to clarify my question just let me know.
Thanks!
I need some help in getting the below formula to work. Right now excel returns the error as that there is an error in the formula but offers no suggestions. Any help would be appreciated. Thanks.
=IF(VLOOKUP($A8,BidData!$A:$BM,22,FALSE))="","No File",(HYPERLINK(VLOOKUP($A8,BidData!$A:$BM,22,FALSE),"File")))
=IF(VLOOKUP($A8,BidData!$A:$BM,22,FALSE))="","No File",(HYPERLINK(VLOOKUP($A8,BidData!$A:$BM,22,FALSE),"File")))
Stab in the dark guys, have a project due but a formula is giving me problems and without the formula I cant build two charts I need.
I am going to explain as best I can what's wrong.
I need to find the average of three sets of figures for three different types of customers. However, the data with the customer keys/types and the figures that need be averaged are in another sheet.
I am trying to use a combination of IF, AVERAGE AND VLOOKUP functions but none seem to be working.
This is the last combination ive tried
=VLOOKUP(a2,avg2,3,false),IF(B5:B11="R"),AVERAGE(C5:C11)
The formula doesnt work because excel instead thinks im referring to B5:B11 in the sheet im typing the formula in and not B5:B11 in the named range avg2 in another worksheet. The same happen with C5:C11 it highlights C5:C11 in the worksheet im typying the formula in and not the avg2 named range in the other worksheet.
B5:B11 represent the customer key for one group of customers and C5:C11 represent the usage level of the same group of customers. I have sorted the table.
Is it possible to use and IF, VLOOKUP, AVERAGE combination and if so can someone please show me where I am going wrong. I need to avoid actually clicking on figures in the other worksheet as I need to avoid relative cell referencing I cant have any avg2! showing in my formulae.
Can anyone assist?
I would be very grateful for any help you can provide.
Thanks in advance.
I am going to explain as best I can what's wrong.
I need to find the average of three sets of figures for three different types of customers. However, the data with the customer keys/types and the figures that need be averaged are in another sheet.
I am trying to use a combination of IF, AVERAGE AND VLOOKUP functions but none seem to be working.
This is the last combination ive tried
=VLOOKUP(a2,avg2,3,false),IF(B5:B11="R"),AVERAGE(C5:C11)
The formula doesnt work because excel instead thinks im referring to B5:B11 in the sheet im typing the formula in and not B5:B11 in the named range avg2 in another worksheet. The same happen with C5:C11 it highlights C5:C11 in the worksheet im typying the formula in and not the avg2 named range in the other worksheet.
B5:B11 represent the customer key for one group of customers and C5:C11 represent the usage level of the same group of customers. I have sorted the table.
Is it possible to use and IF, VLOOKUP, AVERAGE combination and if so can someone please show me where I am going wrong. I need to avoid actually clicking on figures in the other worksheet as I need to avoid relative cell referencing I cant have any avg2! showing in my formulae.
Can anyone assist?
I would be very grateful for any help you can provide.
Thanks in advance.
Hello,
Here is my problem:
Excel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box.
The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work:
=if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))
I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message:
“The List Source must be a delimited list, or a reference to a single row or column”
What should really happen is this:
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only.
Here is my problem:
Excel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box.
The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work:
=if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))
I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message:
“The List Source must be a delimited list, or a reference to a single row or column”
What should really happen is this:
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only.