How can I accurately match up two columns in two sheets to do a vlookup?

Hello!

Here what's up. The columns that I want to match up are not exact matches. Multiple values from sheet1 colA can match up to one value in sheet2 colA by using the first 5 characters of the cell value.


Here is the workbook to go with the sample data set I put below.
HowDoIMatch2.xlsm

As of now, the formula is generating a 2 in the banana row. But there are no bananas documented in sheet2.

How can I get my output to Sheet1 ColB to be accurate without having to create new columns/data with truncated values for the lookup? Is it even possible?

If I use this formula in Sheet1 ColC (then copy downwards /autofill):

Please Login or Register  to view this content.

to grab the Sheet2 colB. It works fine except for in the cases where sheet1 colA values have no match in sheet2 colA (i.e. Sheet1 A4-banana1).
That vlookup formula will output SOMETHING because of the nature of MATCH using either -1 or 1 as a match_type option. I cannot use MATCH if I want to solve this issue...but I do not know what else to do.


Sheet1
a..........................b.........
apple1........=formula
apple2........=formula
apple3........=formula
banana1......=formula
coconut1......=formula
donut1.........=formula


Sheet2
a.....................b.....
apple123...........1
coconut123........2
donut123...........3




I know of a roundabout way to do this but I would like to stay away from "creating new data". (Create/insert a new column in Sheet2. The first row would be =Left($A2,5) then downfilled. This way I can use exact value in the MATCH formula in Sheet1's VLOOKUP.) If VBA is the only way I can "automate" this lookup output to be correct, then that is the road I'll have to take.

That vlookup formula will output SOMETHING because the MATCH formula is grabbing approximate matches.

actually the FALSE will look for an exact match, and the MATCH will look for anything GREATER than the result of the LEFT() - left results in a text string, and text is always greater than a value, so that statement is not strictly accurate

depending on which sheet you want the answers on, why not just try a standard index/match for this? The left() function will truncate the sheet2 values for a search into sheet1, and A2* should give you a wild card for searching into sheet2

Originally Posted by FDibbins

actually the FALSE will look for an exact match, and the MATCH will look for anything GREATER than the result of the LEFT() - left results in a text string, and text is always greater than a value, so that statement is not strictly accurate

depending on which sheet you want the answers on, why not just try a standard index/match for this? The left() function will truncate the sheet2 values for a search into sheet1, and A2* should give you a wild card for searching into sheet2

the FALSE is on the VLOOKUP actually. The incorrect data grab would be from the MATCH function which lies inside VLOOKUP...the issue with the formula is that the row with banana1 is outputting the value that it grabs from coconut123 in sheet2 colE (2). I would like a solution where the formula output will have nothing for banana1 because there is nothing with banana in sheet2.
Also thanks, I did not know that text is of a greater value than numerical entries! Some other things are making sense now :P

how can i truncate the sheet2 values while i am using sheet1 to enter formulas in? would it have to be a specific cell or can i truncate a range? would you mind writing it out?

Yup, I know the FALSWE is for the vlookup().

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.