Solved

excel formula

Posted on 2014-09-05
5
132 Views
Last Modified: 2014-09-05
I am using this formula that works fine until it can't find wjat it is looking for

=IF(R34="Match","",VLOOKUP(LEFT(D34,4),MatchData!L:S,8,))

but if it can't find it returns #N/A

I have tried to add ISERROR but can't get it to work.

Please help instead of #N/A I want it to say Not Found

Thanks
0
Comment
Question by:Jagwarman
5 Comments
 
LVL 37

Accepted Solution

by:
Neil Russell earned 500 total points
ID: 40305828
=IFERROR(IF(R34="Match","",VLOOKUP(LEFT(D34,4),MatchData!L:S,8,)),"Not Found")
0
 
LVL 1

Expert Comment

by:rwniceing
ID: 40305881
=IF(R34="Match","",IF(ISERROR(VLOOKUP(LEFT(D34,4),MatchData!L:S1,8,TRUE)),"NOT FOUND",VLOOKUP(LEFT(D34,4),MatchData!L:S1,8,TRUE)))

You can try this, you need to specifiy exact match with TRUE
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40305882
=IF(R34="Match","",VLOOKUP(VALUE(LEFT(D34,4)),MatchData!L:S,8,FALSE))

I think you need the VALUE command to properly extract a numeric to satisfy the VLOOKUP.
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40305888
by the way, it's much easier for us to work on an example sheet (with no confidential data on it, of course) if you can attach them...
0
 

Author Comment

by:Jagwarman
ID: 40305889
many thanks
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question