HELP! - VLOOKUP with some exact & some non exact matches

[SBassAZ]

I have a report that shows system generated errors. MOST of the errors are unique & I use them to categorize the errors to determine which work-groups they belong to via a table that has the errors & work-groups. My issue is that one of the errors always begins with "Carrier Billed", but from there it changes with value data specific to each error. That error is worked by the same work-group regardless, so I would like to just VLOOKUP that partial text to get my work-group. I'm open to non-VLOOKUP solutions, too.

Thanks in advance,
Susan

 

[Gerald Higgins]

Hi, welcome to the board.

Your request is a bit vague, but perhaps something like

=if(left(A1,14)="Carrier Billed",vlookup("Carrier Billed",B1:C2,2,false),vlookup(A1,B1:C2,2,false))

Where A1 contains your error code, and B1:C2 is your lookup table of error codes and workgroups.
Adapt as required.

 

[DataBlake]

can you reply with an example and an example of what you want the end result to look like?
worksheet names, columns, location of specific cells etc are important too.

otherwise i think you can preface the lookup with an if statement. if(vlookup( or iferror(vlookup(
but i'm not sure what you need specifically without seeing it.

 

[SBassAZ]

Hi, welcome to the board.

Your request is a bit vague, but perhaps something like

=if(left(A1,14)="Carrier Billed",vlookup("Carrier Billed",B1:C2,2,false),vlookup(A1,B1:C2,2,false))

Where A1 contains your error code, and B1:C2 is your lookup table of error codes and workgroups.
Adapt as required.

I think that would work. I was thinking that I needed to include an IF statement, but was unsure how to marry the two.

 

[SBassAZ]

can you reply with an example and an example of what you want the end result to look like?
worksheet names, columns, location of specific cells etc are important too.

otherwise i think you can preface the lookup with an if statement. if(vlookup( or iferror(vlookup(
but i'm not sure what you need specifically without seeing it.

Thanks, Blake! It currently returns #N/A for those errors, so could I use IFNA in combination with the Left formula that Gerald mentioned?

The end result is simply a table with unique errors, invoices, their details, & the name of the team that resolves the errors. (It's a HUGE table that averages about 7000 lines.)

As to the location of the data, the table with the errors list, & then the work-group is on a separate worksheet in the same workbook. The VLOOKUP is based on like column AG & fill in column AH.

 

[DataBlake]

Thanks, Blake! It currently returns #N/A for those errors, so could I use IFNA in combination with the Left formula that Gerald mentioned?

yes you can always use error handling with regular formulas.
So the logic would sound like this in your head "If there is an error, proceed with this formula, if not proceed with this other formula"

=IFERROR(IF(LEFT(A1,14)="Carrier Billed",VLOOKUP("Carrier Billed",B1:C2,2,FALSE),VLOOKUP(A1,B1:C2,2,FALSE)),"error code")

so this code says if there is an error with Gerald's formula return "error code"
where you can replace error code with your formula you were using before.

I also want to elaborate:
with your posted examples you could give is a sample size of say 5 lines
as well the NAMES of the locations for the data.

so an example of all of all the different components would be nice
example of an example:
here is an example of the report mentioned in post 1 which is named "ReportSheet"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Error Messages[/TD]
[TD]Invoice #[/TD]
[TD]Error Team[/TD]
[TD]Client[/TD]
[TD]Account Number[/TD]
[/TR]
[TR]
[TD]Carrier Billed 127[/TD]
[TD]101145[/TD]
[TD]#N/A[/TD]
[TD]Google[/TD]
[TD]54894651[/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]101146[/TD]
[TD]A[/TD]
[TD]Dexter[/TD]
[TD]11225461[/TD]
[/TR]
[TR]
[TD]545[/TD]
[TD]101147[/TD]
[TD]B[/TD]
[TD]Google[/TD]
[TD]54894651[/TD]
[/TR]
[TR]
[TD]Carrier Billed E11[/TD]
[TD]101148[/TD]
[TD]#N/A[/TD]
[TD]Dexter[/TD]
[TD]11225461[/TD]
[/TR]
</tbody>[/TABLE]

here is an example of the error table on sheet "Table1":
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Error Code[/TD]
[TD]Error Team[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]A[/TD]
[TD]Hi[/TD]
[/TR]
[TR]
[TD]545[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and here is what i want to happen when i vlookup in column C for error team
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Error Messages[/TD]
[TD]Invoice #[/TD]
[TD]Error Team[/TD]
[TD]Client[/TD]
[TD]Account Number[/TD]
[/TR]
[TR]
[TD]Carrier Billed 127[/TD]
[TD]101145[/TD]
[TD]A[/TD]
[TD]Google[/TD]
[TD]54894651[/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]101146[/TD]
[TD]A[/TD]
[TD]Dexter[/TD]
[TD]11225461[/TD]
[/TR]
[TR]
[TD]545[/TD]
[TD]101147[/TD]
[TD]B[/TD]
[TD]Google[/TD]
[TD]54894651[/TD]
[/TR]
[TR]
[TD]Carrier Billed E11[/TD]
[TD]101148[/TD]
[TD]D[/TD]
[TD]Dexter[/TD]
[TD]11225461[/TD]
[/TR]
</tbody>[/TABLE]

my current formula is

=vlookup(A2,Table1!A:B,2,false)

 

[SBassAZ]

Thank you both!!! I was able to combine the ideas & have something that works. If you see a cleaner way, let me know.

This is my updated formula.
=IFNA(VLOOKUP($G2,Errors!$A:$C,2,0),IF(LEFT(G2,14)="Carrier Billed","Linked Amt Must Balance",0))

Also, I was going to share the tables, but I can't seem to paste it here in a table, so if you have a tip on that. I will share sample tables.

 

[DataBlake]

Thank you both!!! I was able to combine the ideas & have something that works. If you see a cleaner way, let me know.

This is my updated formula.
=IFNA(VLOOKUP($G2,Errors!$A:$C,2,0),IF(LEFT(G2,14)="Carrier Billed","Linked Amt Must Balance",0))

Also, I was going to share the tables, but I can't seem to paste it here in a table, so if you have a tip on that. I will share sample tables.

if you click on "snapshot" in my forum signature it will bring you to the page explaining how to paste examples on the forum.
my clipboard is being used in a program otherwise i'd paste the link