Multiple VLOOKUP Formulas

[markzasz]

I have a DB that contains over 50K records, and I need to look at the data in column G and write in column H what type of data, I have the following Formula that get almost all the data defined
=IFNA(VLOOKUP([@Column1],$J$3:$K$24,2,0),"PROD")
However I have a section of data that if it starts with T or U I need to designate as "NPROD" I have tried to following formula and it is not placing the name "NPROD". here is what I used
=IFERROR(VLOOKUP([@Column1],$J$3:$K$24,2,0),IFERROR(VLOOKUP($M$3&"*",$G16485,"NPROD"),"PROD"))
it only posts "PROD" when T is found. Can someone please help?

I can send input and tables if requested.

Mark

 

[Joe4]

How about something like this?

[COLOR=#333333]=IF(OR(LEFT([/COLOR][COLOR=#333333][@Column1],1)="T",[/COLOR][COLOR=#333333]LEFT([/COLOR][COLOR=#333333][@Column1],1)="U"),[/COLOR][COLOR=#333333]IFNA(VLOOKUP([@Column1],$J$3:$K$24,2,0),"NPROD"),[/COLOR][COLOR=#333333]IFNA(VLOOKUP([@Column1],$J$3:$K$24,2,0),"NPROD"))[/COLOR]

 

[markzasz]

Joe4,

That worked perfect thank you...
Mark

 

[Joe4]

You are welcome!

 

[markzasz]

is it possible to add another search for "Z" in position 2 of Column1 and when found writhe "TSO"

 

[Joe4]

Just nest another level of IF on to the beginning like I did with the last one.
Give it a try and see how you do. It will be a good test to see if you understand what I did the first time.
Post back with what you tried if you run into trouble.

 

[markzasz]

ok, I have gotten it to read the cell and identify Z in second position, and write TSO by using

MID(H2,2,1)="Z","TSO")

I added it to the formula you provided and I get error

=IF(OR(mid(H2,2,1)="Z","TSO"),(LEFT(H2,1)="T",LEFT(H2,1)="U"),IFNA(VLOOKUP(H2,$M$7:$N$26,2,0),"NPROD"),IFNA(VLOOKUP(H2,$M$7:$N$26,2,0),"PROD"))

 

[Joe4]

No you cannot put it in that OR statement. You need to put it another nested IF statement, i.e.

[COLOR=#333333]=[/COLOR][COLOR=#ff0000]IF(MID(H2,2,1)="Z","TSO",[/COLOR][COLOR=#333333][FONT=Verdana]IF(OR(LEFT([/FONT][/COLOR][COLOR=#333333][FONT=Verdana][@Column1],1)="T",[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]LEFT([/FONT][/COLOR][COLOR=#333333][FONT=Verdana][@Column1],1)="U"),[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]IFNA(VLOOKUP([@Column1],$J$3:$K$24,2,0),"NPROD"),[/FONT][/COLOR][FONT=Verdana]IFNA(VLOOKUP([@Column1],$J$3:$K$24,2,0),"PROD"))[COLOR=#ff0000])[/COLOR][/FONT]

So, think of it this way. You are first checking the 2nd character of H2 and if it is "Z" then return "TSO". Otherwise, return out other calculation we created previously.

 

[markzasz]

Thank you I had tried a few scenarios and on one of them I had similar to what you wrote but for got the IF before the OR. work perfect.... Once again thank you for your help.