Results 1 to 3 of 3

Thread: Help // Vlookup // Is error

  1. #1

    Help // Vlookup // Is error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guys,

    Guys am stuck here and require some assistance.

    Ok, I have a Vlookup with is ISNA,(below) which works fine, what I want it do is increment the value of the error.
    So where i see "Error1" is should move to Error2 if it finds the next error in the match, it should auto increment the value moving forward, I know it can be done, since I had seen somewhere in my previous one, but just couldn't think of it in this cold weather i guess:P. Kindly suggest the ideas...


    =IF(ISNA(VLOOKUP(A3,'LBH All Permits'!A3:G739,6,FALSE)),"Error1",VLOOKUP(A3,'LBH All Permits'!A3:G739,6,FALSE))

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I don't know that I'm totally satisfied with this, but it will work through the use of a helper column.

    I set up a basic table that has the data to look up starting in A3 (like yours), the VLOOKUP result in B3, and a helper column in C3.

    C3 has a formula that reads: =IF(LEFT(B2,5)="Error",1,0)
    B3 has the VLOOKUP I set up: =IF(ISNA(VLOOKUP(A3,$F$2:$G$6,2,FALSE)),"Error"&SUM(C$2:C3)+1,VLOOKUP(A3,$F$2:$G$6,2,FALSE))

    The way it works is that column C is recording a 1 every time the row above has an error in it. Column B then sums all rows from the table start to the current row and adds one to get an accurate error count.

    I'm sure someone else can come up with a formula to avoid the helper column.

    Sample workbook attached.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Quote Originally Posted by Ken Puls View Post
    I don't know that I'm totally satisfied with this, but it will work through the use of a helper column.

    I set up a basic table that has the data to look up starting in A3 (like yours), the VLOOKUP result in B3, and a helper column in C3.

    C3 has a formula that reads: =IF(LEFT(B2,5)="Error",1,0)
    B3 has the VLOOKUP I set up: =IF(ISNA(VLOOKUP(A3,$F$2:$G$6,2,FALSE)),"Error"&SUM(C$2:C3)+1,VLOOKUP(A3,$F$2:$G$6,2,FALSE))

    The way it works is that column C is recording a 1 every time the row above has an error in it. Column B then sums all rows from the table start to the current row and adds one to get an accurate error count.

    I'm sure someone else can come up with a formula to avoid the helper column.

    Sample workbook attached.
    cheers Sir! works like a charm, I played with it myself and somebody also guided me. I found below is slightly easy logic then yours and wanted to share with you.. think we both are using somewhat similar logic tho..

    "=IF(ISNA(VLOOKUP(A2,'LBH All Permits'!A1:C738,3,FALSE)),"Error"&SUMPRODUCT(--(LEFT($B$1:B1,5)="Error"))+1,VLOOKUP(A2,'LBH All Permits'!A1:C738,3,FALSE))"

    again many thanks for the guidance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •