VLOOKUP and IF Function

[Mohrinho]

Hey guys,

I hope you can help me with this problem:

I have created three different VLOOKUP functions that calculate the Co2-Tax Fee on our Cars for different type of tires.
I now want to do an IF Function that checks those 3 values. If they are all the same, it doesn't need to do anything. If they differ though I need any form of Alert.

Here is what I created:

=IF(VLOOKUP(TRIM("Co2-Tax Tire 1");1:1048576;COLUMNS(C:C);)=VLOOKUP(TRIM("Co2-Tax Tire 2");1:1048576;COLUMNS(C:C);)=VLOOKUP(TRIM("Co2-Tax Tire 3");1:1048576;COLUMNS(C:C););"";"! ! !")

As a result I always get ! ! !. Even when the function should be true.

Can anybody see the mistake I've done with the given information.

I don't want to post the file if avoidable, because I don't know how confidential the data is.

Thank you very much for any help!
Greetings from Madrid, Spain :))

[NeedForExcel]

The Syntax of the formula does not make sense to me..

I guess, its better if you can attach a sample workbook, if your data is confidential..

[Mohrinho]

Ok,

Here you have the file with numbers in the relevant field.

The questioned formula is in field C4.

Thank you!

[popipipo]

=if(SUM(C151:C153)/3=C151;"";"alert")
does this work?

[Mohrinho]

No it has to be done through VLOOKUP because this formula is supposed to work on many worksheets, in which the Rows and colums differ...
I can try if the sum thing works though

[popipipo]

=if(SUM(vlookup1+vlookup2+vlookup3)/3=vlookup1;"";"alert")
Then do it this way

[kvsrinivasamurthy]

Try this,

Please Login or Register  to view this content.

[Mohrinho]

popipipo & kvsrinivasamurthy Thank you both for your Input. The Sum formula doesn't work if I have no value in one cell. I tried average=one of the cellls, but it doesn't work neither.
The And( doesn't work as well

[Mohrinho]

The mistake seems to be in the Column part of the vlookups..
if i just put in =VLOOKUP(TRIM("Co2-Tax Tire 1");1:1048576;COLUMNS(C:C);)
it gives me: 'Co2-Tax Tire 1' instead of the value in Column C

[Mohrinho]

Guys it works!
The one mistake that was left was that I used columns(C:C) instead of Column(C:C).
With the formula of kvsrinivasamurthy it now works.

Thank you all!