Conditional formatting and vlookup problem

danno79

New Member
Joined
Oct 28, 2009
Messages
45
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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().
 
Upvote 0
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?
 
Upvote 0
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 :)
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top