nested vlookup needs to remove #name?

[ciojr]

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.

 

[Guest]

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

 

[Domenic]

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},MATCH(VLOOKUP(I14,'ISSUES
(2)'!$B:$M,12,FALSE),{"L","M","H"},0))))

Hope this helps!