Vlookup or Index/Match only matching the first 8 characters.

**CCPopsicle** · 09-17-2014, 12:00 PM

Hi Everyone,

Not sure if this is possible or I will have to amend a folder filepath macro elsewhere.

I have 8 digit references in column A (cell A2 in the non-working formula below) and on another sheet ("Link2") I want to use the 8 digit cell reference in cell A2 to look up against just the first 8 digits of "link2" column A to get the content in the cell in column B and ultimately use this as a hyperlink.

If this were possible it would be something like this :
=INDEX(Link2!B:B,(MATCH(A2,(left,Link2!A:A,8)0))) but it obviously doesn't work. I could use a vlookup nearest match but I can't have any margin for error.

Is there anyway to do this ? I can't think of how this would be possible using any form of count formulas.

Over to anyone who can think outside the box...

Many thanks

**daffodil11** · 09-17-2014, 12:04 PM

The syntax on your MATCH is incorrect and missing arguments, and there's too many parentheses.

Could you attach an example or mock one up?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Perhaps you're trying to MATCH a given reference against a concatenation? INDEX(range,MATCH(ref,range,0)) is the standard format.

**Harribone** · 09-17-2014, 12:07 PM

Quick solution would be to insert a column after 'Link2' column and use the formula:

=LEFT(A2,8)

Then look up your result with vlookup or index/match using this column instead.

**CCPopsicle** · 09-18-2014, 08:47 AM

Daffodil 11,

I've included a mock up of the scenario.

I was aware the index match I suggested was wrong, I was trying to emulate what it was I wanted to achieve.

I wanted to know if there was a way to vlookup or index/match the 8 digit property code in the 1st sheet as an exact match to only the first 8 characters of the filename in the Link2 page.

Harribone, I had thought of using that but I ended up amending a macro to give me a left,8 of the directory fiepath name. It does however slow the macro down considerably and I was hoping to have it auto-run on opening.