# VLOOKUP Highlight

#### seenfresh

##### Well-known Member
I have a VLOOKUP as follows:

=IF(ISNA(VLOOKUP(A3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!\$D:\$H,5,0)),0,(VLOOKUP(A3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!\$D:\$H,5,0)))

I would like to highlight the target cell in '[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'! that is being picked up by the VLOOKUP, any color is okay

I would appreciate any help!

Thx

Sean

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Select the column in the table, go to Format|Conditional Formatting

Select Cell Value Is
Select Equal to
Enter this formula: =VLOOKUP(\$A\$3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!\$D:\$H,5,0)
Click Format and choose your format

Click Ok, Click Ok again.

Thnx

Thank you, but I am would like to highlight the cell in the target worksheet that is being picked up if I

Select Cell Value Is
Select Equal to
Enter this formula: =VLOOKUP(\$A\$3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!\$D:\$H,5,0)

A command tells me that I am not allowed to make reference to outside workbooks!

Is there any other ways I might be able to accomplish this type of reference?

Thx

Yeah sorry, you can't reference outside workbooks....I didn't pick that up when I did my testing and copied your formula to my response....

I think VBA is the only other way.....

Hopefully, someone can help code it for you!

Re: Thnx

Thank you, but I am would like to highlight the cell in the target worksheet that is being picked up if I

Select Cell Value Is
Select Equal to
Enter this formula: =VLOOKUP(\$A\$3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!\$D:\$H,5,0)

A command tells me that I am not allowed to make reference to outside workbooks!

Is there any other ways I might be able to accomplish this type of reference?

Thx

In some unused cell in MonsterOnline_Impressions_Julye06.xls, enter =Ref.xls!SheetRef!A3

where the references above point to your VLOOKUP keyval cell.

Now reference this cell in your C.F.

Re: Thnx

Thank you, but I am would like to highlight the cell in the target worksheet that is being picked up if I

Select Cell Value Is
Select Equal to
Enter this formula: =VLOOKUP(\$A\$3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!\$D:\$H,5,0)

A command tells me that I am not allowed to make reference to outside workbooks!

Is there any other ways I might be able to accomplish this type of reference?

Thx

In some unused cell in MonsterOnline_Impressions_Julye06.xls, enter =Ref.xls!SheetRef!A3

where the references above point to your VLOOKUP keyval cell.

Now reference this cell in your C.F.

Jon, should it be =[Ref.xls]SheetRef!A3

it worked for me after I changed it as above.

Great workaround for the external CF! Thanks.

When I enter this reference I does not allow me to switch to another Wb to select the VLOOKUP key val cell to reference an external window ref. pop-ups for me to save a reference?

=[Ref.xls]SheetRef!A3

I would like to reference to '[National Revenue Allocation.xls]Monster Online (2)'!\$A\$3

Shoud I just do this =D3='[National Revenue Allocation.xls]Monster Online (2)'!\$A\$3
Then Conditional format if TRUE!

The only problem is I have to resort my data to reference properly..

What do you guys think?

Thx alot you guys are awesome!!

Re: Thnx

{snip}

Jon, should it be =[Ref.xls]SheetRef!A3

Yes, fully/correctly referenced.

When I enter this reference I does not allow me to switch to another Wb to select the VLOOKUP key val cell to reference an external window ref. pop-ups for me to save a reference?

=[Ref.xls]SheetRef!A3

I would like to reference to '[National Revenue Allocation.xls]Monster Online (2)'!\$A\$3

Shoud I just do this =D3='[National Revenue Allocation.xls]Monster Online (2)'!\$A\$3
Then Conditional format if TRUE!

The only problem is I have to resort my data to reference properly..

What do you guys think?

Thx alot you guys are awesome!!

If you enter this in D3 of Monster, July 2006 (2) sheet: '[National Revenue Allocation.xls]Monster Online (2)'!\$A\$3

Then you conditional format Vlookup would simply be:

=VLOOKUP(\$D\$3,\$D:\$H,5,0)

....if I am not mistaken, correct Jon?

If it's column D to be highlighted, then

=\$D\$3

If it's column H, then

=Dx=\$D\$3

where x is the row number of the current H cell.

Replies
3
Views
138
Replies
3
Views
348
Replies
4
Views
58
Replies
3
Views
1K
Replies
4
Views
185

1,203,100
Messages
6,053,529
Members
444,669
Latest member
Renarian

### 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.

### Which adblocker are you using?

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

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