• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

excel formula

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
Jagwarman
Asked:
Jagwarman
1 Solution
 
Neil RussellTechnical Development LeadCommented:
=IFERROR(IF(R34="Match","",VLOOKUP(LEFT(D34,4),MatchData!L:S,8,)),"Not Found")
0
 
rwniceingCommented:
=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
 
Danny ChildIT ManagerCommented:
=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
 
Danny ChildIT ManagerCommented:
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
 
JagwarmanAuthor Commented:
many thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now