Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and VLOOKUP formula combined together
{=IF(K8=AA11:AA21,VLOOKUP(K8,'Regular Assessment Table'!
I2:J12,2,FALSE))} The above formula is looking up a K8 text content and returning a vlookup value from a table. Part of the text I enter is "104(a) Citation" and the formula return the correct value. But when I enter "104(d)(1) Citation" the formula returns FALSE. For the life of me I cannot determine why this is happening. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and VLOOKUP formula combined together
The first part of your formula is not doing what you think it's doing. even
though you wrote "K8=aa11:aa21", the next part of the formula requires a single value, so its only passing the first value from the array (in this case, false). As you formula as no criteria given for what to do with a false statement, the function results to false. If you are really wanting to check if K8 is found anywhere in aa1:aa21, and if not, return a blank cell, correct syntax would be: =IF(ISNUMBER(MATCH(K8,AA11:AA21,0)),VLOOKUP(K8, 'Regular Assessment Table'!I2:J12,2,FALSE),"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Don B" wrote: {=IF(K8=AA11:AA21,VLOOKUP(K8,'Regular Assessment Table'! I2:J12,2,FALSE))} The above formula is looking up a K8 text content and returning a vlookup value from a table. Part of the text I enter is "104(a) Citation" and the formula return the correct value. But when I enter "104(d)(1) Citation" the formula returns FALSE. For the life of me I cannot determine why this is happening. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and VLOOKUP formula combined together
On Oct 9, 11:16*am, Luke M wrote:
The first part of your formula is not doing what you think it's doing. even though you wrote "K8=aa11:aa21", the next part of the formula requires a single value, so its only passing the first value from the array (in this case, false). As you formula as no criteria given for what to do with a false statement, the function results to false. If you are really wanting to check if K8 is found anywhere in aa1:aa21, and if not, return a blank cell, correct syntax would be: =IF(ISNUMBER(MATCH(K8,AA11:AA21,0)),VLOOKUP(K8, 'Regular Assessment Table'!I2:J12,2,FALSE),"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Don B" wrote: {=IF(K8=AA11:AA21,VLOOKUP(K8,'Regular Assessment Table'! I2:J12,2,FALSE))} The above formula is looking up a K8 text content and returning a vlookup value from a table. *Part of the text I enter is "104(a) Citation" and the formula return the correct value. *But when I enter "104(d)(1) Citation" the formula returns FALSE. *For the life of me I cannot determine why this is happening. *Any ideas?- Hide quoted text - - Show quoted text - Thanks Luke the formula worked like a charm. I have another formula I have to add to search more criteria hopefully I can get it right now. Thanks a lot Don |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and VLOOKUP formula combined together
On Oct 9, 12:47*pm, Don B wrote:
On Oct 9, 11:16*am, Luke M wrote: The first part of your formula is not doing what you think it's doing. even though you wrote "K8=aa11:aa21", the next part of the formula requires a single value, so its only passing the first value from the array (in this case, false). As you formula as no criteria given for what to do with a false statement, the function results to false. If you are really wanting to check if K8 is found anywhere in aa1:aa21, and if not, return a blank cell, correct syntax would be: =IF(ISNUMBER(MATCH(K8,AA11:AA21,0)),VLOOKUP(K8, 'Regular Assessment Table'!I2:J12,2,FALSE),"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Don B" wrote: {=IF(K8=AA11:AA21,VLOOKUP(K8,'Regular Assessment Table'! I2:J12,2,FALSE))} The above formula is looking up a K8 text content and returning a vlookup value from a table. *Part of the text I enter is "104(a) Citation" and the formula return the correct value. *But when I enter "104(d)(1) Citation" the formula returns FALSE. *For the life of me I cannot determine why this is happening. *Any ideas?- Hide quoted text - - Show quoted text - Thanks Luke the formula worked like a charm. *I have another formula I have to add to search more criteria hopefully I can get it right now. Thanks a lot Don- Hide quoted text - - Show quoted text - I tried to modify the above formula to be able to read two lookup values and return the corrrect lookupvalue based upon this information. My new formula is below. Well needless to say it will not work. {=IF(OR(K8=AA11:AA21,ISTEXT(K10)),0,VLOOKUP(K8,'Re gular Assessment Table'!I2:J12,2,FALSE))} What I am trying to accomplish is to be able to look at two different lookup values (K8 & K10) and return the vlookup value calculation accordingly. There is always a lookup value in K8. Normally the K10 value is blank but occassionally there will be a time when there is a K8 and K10 value to calculate. For whatever reason I cannot get the formula right. Each of the K8 and K10 values are in a drop down list but the first cell in the K10 list is blank. When I did the validation list for K10 I unchecked the box to ignore blanks. I thought that might be my problem but it was not. Let me also say that the K8 &K10 lookup values are text strings. What am I doing wrong,Can anybody get me back on the right path? Thanks Don |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with IF and VLOOKUP combined | Excel Worksheet Functions | |||
VLOOKUP combined with AND | Excel Discussion (Misc queries) | |||
vlookup combined with AND-function | Excel Worksheet Functions | |||
vlookup and match combined? | Excel Worksheet Functions | |||
vlookup / len function combined | Excel Discussion (Misc queries) |