Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested vlookup needs to remove #name?
I have the following
=IF(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)="M", 20,IF(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)="H", 40,ifIF(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)="L",12,0))) if no match is found, i get #NAME? how can i replace this with 0 if no match is found Thanks in advance and happy easter. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested vlookup needs to remove #name?
Assuming you pasted your formula correctly, you receive the #NAME error
because you double entered the last IF function. ifIF should be IF You might want to try this formula. =IF(ISNUMBER(MATCH(I14,'ISSUES (2)'!$B:$B,0)),SUMPRODUCT((VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)={"M","H","L"})*{20,40,12}),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: I have the following =IF(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)="M", 20,IF(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)="H", 40,ifIF(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)="L",12,0))) if no match is found, i get #NAME? how can i replace this with 0 if no match is found Thanks in advance and happy easter. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested vlookup needs to remove #name?
Maybe...
=INDEX({12,20,40},MATCH(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE),{"L","M","H"},0)) Or, to trap #N/A error values, define (Insert Name Define) BigNum as 9.99999999999999E+307, then try... =LOOKUP(BigNum,CHOOSE({1,2},0,INDEX({12,20,40},MAT CH(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE),{"L","M","H"},0)))) Hope this helps! In article .com, wrote: I have the following =IF(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)="M", 20,IF(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)="H", 40,ifIF(VLOOKUP(I14,'ISSUES (2)'!$B:$M,12,FALSE)="L",12,0))) if no match is found, i get #NAME? how can i replace this with 0 if no match is found Thanks in advance and happy easter. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF with a VLOOKUP? | Excel Worksheet Functions | |||
Can Someone Help me With a Nested VLOOKUP | Excel Discussion (Misc queries) | |||
Nested vlookup | Excel Discussion (Misc queries) | |||
nested ifs or vlookup or ? | Excel Discussion (Misc queries) | |||
Nested vlookup? | Excel Worksheet Functions |