vlookup error

[KAYAK]

Hello Forum,

I am new to this kind of function, not sure if i have posted in the right place. I am getting an #na error with the following, can anyone please explain where i am going wrong.

=IF(B12>999,(VLOOKUP(C2,RATEMASTER!$C$2:$H$20,6,FALSE)+B12*VLOOKUP(C2,RATEMASTER!$C$2:$H$20,7,FALSE))*VLOOKUP(C2,RATEMASTER!C2:H20,8,FALSE),IF(B12=500,VLOOKUP(C2,RATEMASTER!$C$2:$H$20,5,FALSE)*VLOOKUP(C2,RATEMASTER!$C$2:$H$20,8,FALSE),IF(B12=250,VLOOKUP(C2,RATEMASTER!$C$2:$H$20,4,FALSE)*VLOOKUP(C2,RATEMASTER!$C$2:$H$20,8,FALSE),0)))

[mmmarks]

Hi,
Whatsoever BOLD , there is a proble C2:H20 .means, the table having 6 coloum but you are trying to retrieving data beyond the table which is not exist in range.


=IF(B12>999,(VLOOKUP(C2,RATEMASTER!$C$2:$H$20,6,FALSE)+B12*VLOOKUP(C2,RATEMASTER!$C$2:$H$20,7,FALSE) )*VLOOKUP(C2,RATEMASTER!C2:H20,8,FALSE),IF(B12=500,VLOOKUP(C2,RATEMASTER!$C$2:$H$20,5,FALSE)*VLOOKUP (C2,RATEMASTER!$C$2:$H$20,8,FALSE),IF(B12=250,VLOOKUP(C2,RATEMASTER!$C$2:$H$20,4,FALSE)*VLOOKUP(C2,R ATEMASTER!$C$2:$H$20,8,FALSE),0)))

[Russell Dawson]

Hi all,

Well spotted mmmarks. Just to make it a little clearer.

The range from C2 to H20 only covers 6 columns and as mmmarks rightly points out you are asking for 7 and 8 columns to be looked at.

Cheers

Russell

[mmmarks]

Hi all,

Well spotted mmmarks. Just to make it a little clearer.

The range from C2 to H20 only covers 6 columns and as mmmarks rightly points out you are asking for 7 and 8 columns to be looked at.

Cheers

Russell

Thank U Russeell .

[KAYAK]

Hi mmmarks,
Thanks for picking up the error, I have tried to reduce each one by 2 but it still returns an error. I have attached the file i am working on if you have time would you look at it and tell me where i have gone wrong.

[Russell Dawson]

Please Login or Register  to view this content.

You had C2 instead of A12 in LOOKUP VALUE.

[KAYAK]

Hello Russell,

Many thanks for your help and everyone else as well.

regards

Keith

[Russell Dawson]

A fine team effort. Don't forget to mark thread as solved please.

Cheers

Russell