VLOOKUP Highlight

[seenfresh]

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

 

[seenfresh]

Thx

This is '[National Revenue Allocation.xls]Monster Online (2)'! where it has the VLOOKUP into the other!

Yes only the stations in this wb have the stations in the corresponding D column, in [2nd]wb '[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'! Some stations in this wb are not present in [1]Wb that's ok.

National Revenue Allocation.xls
	A	B	C	D	E
3	FOX43	Lin	0%	0.46	0.67
4	KAIT	Raycom	50%	56.89	83.67
5	KASA	Raycom	50%	58.19	85.57
6	KATC	Cordillera	50%	848.05	1,247.13
7	KCAU	Citadel	50%	49.45	72.72
Monster Online (2)

 

[seenfresh]

Thx

This is '[National Revenue Allocation.xls]Monster Online (2)'! where it has the VLOOKUP into the other!

Yes only the stations in this wb have the stations in the corresponding D column, in [2nd]wb '[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'! Some stations in this wb are not present in [1]Wb that's ok.

National Revenue Allocation.xls
	A	B	C	D	E
3	FOX43	Lin	0%	0.46	0.67
4	KAIT	Raycom	50%	56.89	83.67
5	KASA	Raycom	50%	58.19	85.57
6	KATC	Cordillera	50%	848.05	1,247.13
7	KCAU	Citadel	50%	49.45	72.72
Monster Online (2)

 

[seenfresh]

Thnx

This is '[National Revenue Allocation.xls]Monster Online (2)'! where it has the VLOOKUP into the other!

Yes only the stations in this wb have the stations in the corresponding D column, in [2nd]wb '[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'! Some stations in this wb are not present in [1]Wb that's ok.

National Revenue Allocation.xls
	A	B	C	D	E
3	FOX43	Lin	0%	0.46	0.67
4	KAIT	Raycom	50%	56.89	83.67
5	KASA	Raycom	50%	58.19	85.57
6	KATC	Cordillera	50%	848.05	1,247.13
7	KCAU	Citadel	50%	49.45	72.72
Monster Online (2)

 

[just_jon]

And you want, in the Julye06.xls wish to have highlighted the matches for 1] everything in this column A, 2] just A3 [FOX43], or 3] something else?

 

[seenfresh]

Wish to highlight every Station in column D of wb[2] [MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!

1] That is being picked up by the VLOOKUP in wb[1] National Revenue Allocation.xls

my range in from D3:D900 in wb[2],

2] But I would just like to highlight the visible cells(bold) stations in column D of wb[2]

 

[just_jon]

Wish to highlight every Station in column D of wb[2] [MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!

1] That is being picked up by the VLOOKUP in wb[1] National Revenue Allocation.xls

my range in from D3:D900 in wb[2],

2] But I would just like to highlight the visible cells(bold) stations in column D of wb[2]

And the bold ones are always, and only, the first instances of the station?

If not, then are the rows they are on identifiable in any other unique way?

 

[seenfresh]

And the bold ones are always, and only, the first instances of the station?

Yes, the bold station names are the first instance for VL purposes and they contain totals which have hidden cells that are subtotals and 2nd, 3rd... instances

Thnx

 

[just_jon]

OK, try --

1] create a new sheet Admin in the Julye06.xls file. Name it Admin

2] in column A of Admin, in A3 enter and copy down to row#900: =INDEX([National Revenue Allocation.xls ]'Sheet name here'!$D:$D,ROW())

3] Highlight those ~900 cells and click Insert | Name | Define, and in the Names box exter TheList

4] Go to sheet Monster, July 2006 (2) and highlight the range to apply C.F. Assuming here it is all of column D

5] Click Format | Conditional Format and use: =AND(MATCH($D1,$D:$D,0)=ROW(),ISNUMBER(MATCH($D1,TheList,0)))

Adjust the D1 ref above to reflect the 1st cell in the range actually chosen in step #4 above. If it is not the whole column, also adjust the D:D reference to the actual range.

 

[seenfresh]

Great Thanx

I Adjusted the formula to: =AND(MATCH($H3,$H:$H,0)=ROW(),ISNUMBER(MATCH($H3,TheList,0)))

column H had the values to be matched, works great!

I have one question when I used the =INDEX('[National Revenue Allocation.xls]Monster Online (2)'!$D:$D,ROW())

It is referencing to my wb[1] with the V/L's I am wondering how it returns the values from this wb[1] in column H when the formula's range is $D:$D and their is no specified offset? It works good, just curious?

Once again thanks for all your help! you are the master!

Cheers!

Sean

 

[just_jon]

Your lookup key, the 1st argument in the VLOOKUP statement, is D3, D4, etc.

So, we need to yank those values over into a list in this book. I chose to use INDEX/ROW to avoid #REF errors if rows were deleted while both books were open. If that would never happen, better to use: '[National Revenue Allocation.xls ]'Sheet name here'!$D3 copied down.

Those -- FOX43, etc, are what you want to match in the current workbook, 1st instance.