Vlookup help

[RTolentino]

Hello All...new to the forum

the formula below isn't referencing the last vlookup function. When the value is over 1000 it gives me a #ref error. Any help is appreciated. Thank you

=IF(A16>0,IF($M$39<50,VLOOKUP(A16,'FY16 Pricing'!A:G,3,FALSE),IF($M$39<100,VLOOKUP(A16,'FY16 Pricing'!A:G,4,FALSE),IF($M$39<300,VLOOKUP(P16,'FY16 Pricing'!A:G,5,FALSE),IF($M$39<500,VLOOKUP(P16,'FY16 Pricing'!A:G,6,FALSE),IF($M$39<1000,VLOOKUP(A16,'FY16 Pricing'!A:G,7,FALSE),VLOOKUP(A16,'FY16 Pricing'!A:G,8,FALSE)))))),"")

 

[Marcelo Branco]

Hello All...new to the forum

the formula below isn't referencing the last vlookup function. When the value is over 1000 it gives me a #ref error. Any help is appreciated. Thank you

=IF(A16>0,IF($M$39<50,VLOOKUP(A16,'FY16 Pricing'!A:G,3,FALSE),IF($M$39<100,VLOOKUP(A16,'FY16 Pricing'!A:G,4,FALSE),IF($M$39<300,VLOOKUP(P16,'FY16 Pricing'!A:G,5,FALSE),IF($M$39<500,VLOOKUP(P16,'FY16 Pricing'!A:G,6,FALSE),IF($M$39<1000,VLOOKUP(A16,'FY16 Pricing'!A:G,7,FALSE),VLOOKUP(A16,'FY16 Pricing'!A:G,8,FALSE)))))),"")

Welcome to Mr. Excel

The range A:G has 7 columns. The last part of your formula (when M39 >=1000) uses 8 as col_index_num (see in red).
Maybe you have to use range A:H.

M.

 

[RTolentino]

Wow thanks...I knew it would be something simple. Thanks again.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.