Sorting help-vlookup?

  • Thread starter Thread starter dzelnio
  • Start date Start date
D

dzelnio

Worksheet2.
Column A is the name of a project
Column B is a date
Column C is word count
Column D is photo count
There are about 400 rows of information under those headings.

My problem is that Column A is messed up. The same project may be
named two or three different ways. I need to find the name of a
project by keyword (vlookup?) and by the latest date. Then I need
that row to appear on a new Worksheet3 under official names that I
define.

If this is a vlookup, I'd really appreciate a formula that allows me
to look up the keyword "Clack" and put that row under my row called
Clickety Clack on Worksheet3.

I know this doesn't work. Can anyone fix it?
=VLOOKUP(Clack,Worksheet2!A1:D2200,2)
 
I think I'd spend time cleaning up that data, but maybe you could use something
like:

=VLOOKUP("*Clack*",Worksheet2!A1:D2200,2)

The asterisks are wild cards and the first row that has clack in column A will
be returned.
 
Problem with cleaning up data is training 20 people to name files the
right way (hrmph).

=VLOOKUP("Clack",Sheet2!A:D,2)
works great except it finds data from exactly one cel above my
targeted data. How can I correct that?

Dave

PS: What is a wildcard?
 
Add a zero or false so's your vlookup looks for an exact match.

=VLOOKUP("Clack",Sheet2!A:D,2,0) or =VLOOKUP("Clack",Sheet2!A:D,2,FALSE)

If value not found will return #N/A which you can deal with by trapping for it.

=IF(ISNA(=VLOOKUP("Clack",Sheet2!A:D,2,0)),"",VLOOKUP("Clack",Sheet2!A:D,2,0))

A wildcard is the asterisk(*) Dave posted in his formula.

Used in place of an actual string.

"*Clack*" will look to any file with "Clack" in the name.


Gord Dibben MS Excel MVP

Problem with cleaning up data is training 20 people to name files the
right way (hrmph).

=VLOOKUP("Clack",Sheet2!A:D,2)
works great except it finds data from exactly one cel above my
targeted data. How can I correct that?

Dave

PS: What is a wildcard?
Click to expand...
 
GOT IT!

Thanks all.

Dave


Add a zero or false so's your vlookup looks for an exact match.

=VLOOKUP("Clack",Sheet2!A:D,2,0) or =VLOOKUP("Clack",Sheet2!A:D,2,FALSE)

If value not found will return #N/A which you can deal with by trapping for it.

=IF(ISNA(=VLOOKUP("Clack",Sheet2!A:D,2,0)),"",VLOOKUP("Clack",Sheet2!A:D,2, 0))

A wildcard is the asterisk(*) Dave posted in his formula.

Used in place of an actual string.

"*Clack*" will look to any file with "Clack" in the name.

Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top