Vlookup

[Stuck14]

Looking for a drop of help with the VLOOKUP function, or suggestions for an alternative.

I have two sets of data.

The first holds the correct values, by part. The second has the same part but with an aged column.

I need to feed the age from the second to the first report, giving me an aged inventory report.

I have put the VLOOKUP formula in and i get a number of problems:-

1) if i enter the formula and use the "true" argument most of the data brought over are correct, but a number are incorrect
2) if i enter the formula and use the "false" argument all lines come back #N/A


These are huge data sets, and it was just by chance, when validating the results that it came to my attention that the dates were incorrect in some lines.

Advice would be much appreciated.

[Jonmo1]

You can try adding a wildcard to the lookup value
=VLOOKUP(A1&"*",range,colindex,FALSE)

So if A1 is "Hello", but in the first column of the lookup range you have "Hello There", that would be a match.

Can you post 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

[Stuck14]

Thanks Jonmo1.

I just popped a bit of data into my excel and put the VLOOKUP formula in and it works a treat. When i put an extra space in it comes up with the NA result.

This makes me think that the data is not exactly formatted the same arggghhhh this makes me worried, i already did the find and replace to remove extra spaces and i cant think what else might be causing the error.

[Jonmo1]

Why did you put in an extra space?

Post a sample book..

[Stuck14]

VLOOKUP.xlsx

Not sure if thats attached.

When i say i added another space, what i meant was i added a space in the part text to see why i was getting the NA.

This is an exceptionally simplified version of my actual data.

The VLOOKUP formula does work but i get the feeling that there is a problem with the actual text if you know what i mean.

[Jonmo1]

I don't see a problem...
After removing the "intentionally added" extra spaces from A2 and A3, all 4 vlookups work just fine..

[Stuck14]

thats what i though! So i double checked my formula in my actual workbook and its still not working.

Couple of points which might give rise to another formula (suggestions always appreciated!)

There are parts in both worksheets which are not relevant.
The data is not sorted, because of the problem above.

But like i say the data set is massive. Page one is 23000 lines and second page is 39000 lines.

Is there another look up formula that might work do you know? I can physically check all these by tomorrow, this should have been done today!!! But when i checked my results it screamed ERROR at me and my mind when into overdrive!

[Jonmo1]

Yes, that's a problem when using FALSE

I think the problem is that it isnt an exact match.

And This is a problem when using TRUE

The data is not sorted, because of the problem above.

You seem to be stuck between a rock and a hard place.

The wildcards+FALSE was the best option I could think of, but that apparently doesn't work in your case.
Afraid I can't offer any further help without seeing a more realistic sample set of data.

[Jonmo1]

Your formula is fine.
=VLOOKUP(A2,Aging!A:C,3,FALSE)

If you get #N/A, it means the value in A2 doesn't have an EXACT match in column A on the Aging sheet.
Spelling is off, or there are extra spaces in the cells.

You can try the wildcard method as I suggested, but it can be expanded to be used at both front and end of A2
=VLOOKUP("*"&A2&"*",Aging!A:C,3,FALSE)

[Stuck14]

Tried wildcard. Again it brought the wrong date over.

I think the problem is that it isnt an exact match. I wonder if it matters that one report came out as an excel export and one came out as a CSV orginally? I have copied the test over to work to check for spaces etc, but it appears clean.

Oh lordy this is driving me totally crazy!

Thanks for the tips though