VLOOKUP: ignore first 58 characters in cell, return value, export XML

**pmolter** · 02-28-2010, 12:05 AM

I have 2 spreadsheets:
1 - list of filenames, title, description
2 - list of filenames with paths (and additional information)

Can't figure out how to write the VLOOKUP so it ignores the first 58 characters (which are the path) and matches the filename so I can return the title: description in spreadsheet 2, then export to XML.

Spreadsheet 2 originated as an XML file so there's that to work with as well if it makes a difference.

Any help would be greatly appreciated. Thank you!

**teylyn** · 02-28-2010, 12:22 AM

Where would you want Vlookup to ignore the first 58 characters? In the lookup value or the lookup table first column? A data sample might do wonders to help comprehension. One of the good things in this forum is that you can upload files.

You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

**rwgrietveld** · 02-28-2010, 06:41 AM

Hi Teylyn,

here's your upload. Maybe you can do a better job than I.

HTML Code:

=VLOOKUP(MID(A2,1+MAX(IF(MID(A2,ROW($A$1:$A$100),1)="\",ROW($A$1:$A$100))),LEN(A2)-MAX(IF(MID(A2,ROW($A$1:$A$100),1)="\",ROW($A$1:$A$100)))),Sheet1!A1:C4,2,FALSE)

My formula lookup up the last "\" and reads from that point on.

I've seen other (better) constructions, but I leave it to others to optimize.

**martindwilson** · 02-28-2010, 08:28 AM

alternatively try
=VLOOKUP(TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",50)),30)),Sheet1!$A$1:$C$4,2,FALSE)
edit
of course if you know file path is always 58 char including the final \ then =VLOOKUP(MID(A2,59,255),Sheet1!$A$1:$C$4,2,FALSE)

**teylyn** · 02-28-2010, 08:32 AM

I have a feeling that the lookup value is only the file name, but the first column of the lookup table contains the whole path.

Up to the OP to clarify.

**martindwilson** · 02-28-2010, 08:38 AM

yes you could be correct hmm then its simply this format
=VLOOKUP("*"&A2,Sheet2!$A$14:$B$17,2,FALSE) where a2 =filename.xls Sheet2!$A$14:$B$17 =full paths with filename
as long as the filename contains the file extension .xls .txt,.doc and so on it will find it as there couldnt be instances like that in the file path.

**pmolter** · 02-28-2010, 11:15 AM

Thanks for the responses!

Due to the difference in the file names I can't lookup past the final / unfortunately...it has to match past character 46 (sorry about the 58...was late and my math was broke) and somehow ignore the underscores. I guess I could do a find/replace to remove spaces and replace with underscores to eliminate that issue.

I've attached the two spreadsheets. Copy of gallery.xlsx is where I was placing the VLOOKUP to return the match from column 2 of the other. Would there be an easy way to return both columns 2 and 3 separated by ": "?