Pls Help : Nested IF limit

Copper Contributor

Hi Expert,

 

Can anyone help solve as I had more than 7 IF conditions which excel allow.

 

My problem is, i need lookup with a table using first 12 characters, if not found then first 11 character, then first 10,..... first 3.....

 

Formula:

=IFNA(VLOOKUP(LEFT(I90,12),'Detailed List'!L:M,1,0),

IFNA(VLOOKUP(LEFT(I90,11),'Detailed List'!L:M,1,0),

IFNA(VLOOKUP(LEFT(I90,10),'Detailed List'!L:M,1,0),

IFNA(VLOOKUP(LEFT(I90,9),'Detailed List'!L:M,1,0),

IFNA(VLOOKUP(LEFT(I90,8),'Detailed List'!L:M,1,0),

IFNA(VLOOKUP(LEFT(I90,7),'Detailed List'!L:M,1,0),

IFNA(VLOOKUP(LEFT(I90,6),'Detailed List'!L:M,1,0),

IFNA(VLOOKUP(LEFT(I90,5),'Detailed List'!L:M,1,0),

IFNA(VLOOKUP(LEFT(I90,4),'Detailed List'!L:M,1,0),

IFNA(VLOOKUP(LEFT(I90,3),'Detailed List'!L:M,1,0),

"NotFound"))))

1 Reply
I believe this may be one way (Ctrl+Shift+Enter after keying into the formula bar), but including the entire column will slow down the calculation time. I would limit the range, if possible.

=IFERROR(LEFT(I90,LARGE(IF(ISNUMBER(SEARCH(LEFT(I90,TRANSPOSE(ROW((INDIRECT("3:12"))))),'Detailed List'!L:L)),TRANSPOSE(ROW((INDIRECT("3:12")))),""),1)),"Not Found")