Conditional formatting and vlookup problem

[danno79]

Hi All I'm wrestling with using vlookups to generate conditional formatting - heres the scenario; i want the conditional format to check whether the value in the cell adjacent to the vlookup result is greater than 0 and if so the formatting is true. At the monent I have the formula in the conditional formatting as follows;

=IF((VLOOKUP(F47,$BY$5:$BZ$44,2)>0),TRUE, FALSE)

I'm sure this is not right as it produces quite erratic results; some instances of data work but others don't. For info the data in coulumn BY that are being looked up are 3 letter codes and not sorted alphabetically, the data in column BZ is an unsorted number or blank - does this have any bearing n the vlookup?

Any suggestions welcomed

Thanks

 

[drsarao]

From Excel help:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.
Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

• If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

Erratic results are because table_array is NOT sorted and range_lookup is TRUE (default value).
Changing range_lookup to False will require an EXACT match of the lookup_value in the table_array. Otherwise #N/A is returned which may not what you want for the conditional format. This can of course be checked by ISNA().

 

[danno79]

Thanks for your reply drsarao,

I have changed the formula as per you suggestion to

=IF((VLOOKUP(A1,$L$1:$M$10,2, FALSE))>0,TRUE,FALSE)

and sorted the first column of the table_array into ascending order but this still doesn't solve the problem. Outside of the conditional formatting, the vlookup part finds the correct value, the problem seems to be with the IF statement but i can't figure out what?

 

[danno79]

Ok so I have solved it using the following;

=IF(ISNUMBER(VLOOKUP(F47,$BY$5:$BZ$44,2,FALSE)),TRUE, FALSE)

obviosly something about the logical test for the vlookup result was cousing a problem

thanks for your input

 

[T. Valko]

Ok so I have solved it using the following;

=IF(ISNUMBER(VLOOKUP(F47,$BY$5:$BZ$44,2,FALSE)),TRUE, FALSE)

obviosly something about the logical test for the vlookup result was cousing a problem

thanks for your input

You can reduce that to:

=COUNT(VLOOKUP(F47,$BY$5:$BZ$44,2,0))