Findind/matching/highlighting data

**nikolat** · 04-13-2007, 04:34 AM

Hi all,

I'm very noobish when it comes to excel, so I came here in hope you can help me out with this.

My task & problem is this:

I have one workbook containing music download report on one sheet containing over a 1000 titles.

On the second sheet I have a list of 32 titles, that I have to find/highlight out of those 1000.

(or add something to a cell aside so I could sort those items by it).

This task seems like some casual operation that can arise, but I haven't found simple solution, and yea - I do it by hand quite few times a month

I've attached example. Please, help noob out

Thank you!

**PeterB** · 04-13-2007, 05:00 AM

Put this equation in cell C1 on the Wanted List sheet :

="Row "&MATCH(B1,'total report'!$B$1:$B$1167,0) & " from top"

Then simply copy it down to cover the number of rows you have.

If you add to the total report sheet, you may have to update the part of the equation that says :

$B$1:$B$1167

to extend its coverage beyond cell B1167.

**nikolat** · 04-13-2007, 07:28 AM

Hi Peter,

thank you for a quick reply.

Excel reports an error when I place a function. It marks B1,'total.

Tried it both in excell 2003 and 2007...

Seems like it's not happy with a lookup value. I tried some combinations I could have think of, but no luck

**ktchegi** · 04-16-2007, 02:52 AM

I have tried your example using "VLOOKUP" and it works fine. Try this link for an excellent tutorial.
http://www.datapigtechnologies.com/f.../vlookup.html:)

**PeterB** · 04-16-2007, 03:58 AM

Nikolat,

Sorry about delay in replying - I've got forum set up on PC at work.

What I forgot to say in the first reply is that you will ned to go through your lists and get rid of all the trailing spaces in many of the cells. When I use any of the lookup functions VLOOKUP, HLOOKUP, MATCH, etc. I tend to use them to get an exact match between what you are using as the lookup value and what is in the cells in the lookup area.

However, I don't know why you got the 'total in B1, I just tried on a new copy of your file and got #N/A when the trailing spaces were still there. Did you copy the formula using Ctrl C/Ctrl V? Or did you type it in? Have you double-checked for any errors? I've got Excel 2002 SP3, so I might expect 2003, at least, to be OK with the equation - there's nothing special about it really.

Peter

Added copy of your file with equations added.