stuck on vlookup issue

Using a sheet to organize data to fit an exact order and fill in null values when data is unavailable. Data is being pasted from an exported sheet in another program.

destination formula returns #NAME? as =IF(ISNA,0,(VLOOKUP(A8,H3:O21,2,FALSE))) and obviously a 0 value for =IF(ISERROR(VLOOKUP(A9,$H$3:$U$68,2,FALSE)),0)

Formulas in Column D support the lookup column A

Column A is a three digit billing code stored as text (ex. 001)
Column C is a description
Column D is the monetary value

Past from an external sheet in cell H3 extending over 1 column and down as many as needed is the table_array.

my problem. the values in Column H are a combination of Code and description. The descriptions may not be an exact match to Column C but the three digit codes are a match.

how do I rework my vlookup to search only the first 3 characters of column H?


LVL 2
synetronAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you are using ISNA in the first formula, but there are no function brackets, hence the Name? error. Do you perhaps want

=IF(ISNA(VLOOKUP(A8,H3:O21,2,FALSE)),0,VLOOKUP(A8,H3:O21,2,FALSE))

It might be helpful if you could post a small data sample as an Excel file and explain in context.


cheers, teylyn

0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
To search only the first three digits of column H, you would need a helper column with a formula like

=left(H3,3)

If you want to use VLookup, then that helper column needs to be to the left of the data. Otherwise, you would need to use Index/Match.

0
barry houdiniCommented:
You can use a "wildcard" in VLOOKUP, e.g. "*"

=VLOOKUP(A8&"*",H$3:I$21,2,FALSE)

That will match the 3 letter code in A8 with a value in H3:H21 that starts with those 3 letters....and will return the corresponding value in I3:I21. Add error checking as per teylyn's suggestion

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

synetronAuthor Commented:
so something like

=IF(ISNA(VLOOKUP(LEFT(A8,H3:O21),2,FALSE)),0,VLOOKUP(LEFT(A8,H3:O21),2,FALSE))

??

i've uploaded a quick example of what i'm attempting.
Book1.xls
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Did you see Barry's suggestion? That's much better than a helper column. Applied to your sample file, starting in D3

=IF(ISNA(VLOOKUP(A3&"*",H3:O21,2,FALSE)),0,VLOOKUP(A3&"*",H3:O21,2,FALSE))

copy down. Points to Barry, please.

cheers, teylyn
0
synetronAuthor Commented:
the wildcard worked. Thanks very much for the fast attention.
much appreciated
0
barry houdiniCommented:
You might need to avoid a "false positive" when the cell is blank in which case try

=IF(A3="","",IF(ISNA(VLOOKUP(A3&"*",H$3:I$21,2,FALSE)),0,VLOOKUP(A3&"*",H$3:I$21,2,FALSE)))

regards, barry
0
synetronAuthor Commented:
The false positive tip was another life saver... it popped up as soon as i applied it to the final sheet and propagated all the way down the column. thanks again to both of you, very helpful... and, I learned a little something! it's a good day.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.