Help // Vlookup // Is error

[nicci113]

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. 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))

 

[Ken Puls]

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.

 

[nicci113]

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