Excel formula

[Rasscal]

Hi folks

im really new to excel & wondered if someone would help me with a formula

exampled below im trying to find "sales order" on sheet 2 within "order number" in sheet 1 IF warehouse error equals True display username, im asuming this is a vlookup formula but i cannot get it to work with my basic knowledge of excel.

Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Order number[/TD]
[TD]Warehouse Error[/TD]
[TD]User name[/TD]
[/TR]
[TR]
[TD]TT4002112[/TD]
[TD]True[/TD]
[TD](RESULT HERE)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]TT4053763[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]False[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sales Order[/TD]
[TD]user name[/TD]
[/TR]
[TR]
[TD]4002112[/TD]
[TD]micky mouse[/TD]
[/TR]
[TR]
[TD]4053763[/TD]
[TD]daffy duck[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

in C2
=VLOOKUP(RIGHT(Sheet1!A2,7)+0,Sheet2!A$2:B$3,2,0)
and copy down

 

[Rasscal]

wow super quick & worked first time after i put sales order in my first column


=VLOOKUP(RIGHT(E2,7)+0,'FY19 PPD Data'!A2:R1048576,17,0)

I understand the above formula apart from the hightlighted above, what does this mean?

And to go one step further, anyway to dispaly a user name if TRUE but N/A if False

many thanks

 

[Swayzy]

Right/left function returns a string and +0 converts it to a number

 

[Special-K99]

=IFERROR(VLOOKUP(RIGHT(Sheet1!A2,7)+0,Sheet2!A$2:B$3,2,0),"N/A")

 

[Rasscal]

=IFERROR(VLOOKUP(RIGHT(Sheet1!A2,7)+0,Sheet2!A$2:B$3,2,0),"N/A")

sorry i think you misunderstood from my poor explanation the "TRUE or FALSE" statments are data inputted manually not errors created by other formulas

which means the above IFERROR is not working correctly

Sheet 1

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Order number[/TD]
[TD]Warehouse Error[/TD]
[TD]User name[/TD]
[/TR]
[TR]
[TD]TT4002112[/TD]
[TD]True[/TD]
[TD](RESULT HERE)[/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"][TABLE="class: cms_table, width: 80"]
<tbody>[TR]
[TD]TT4053763[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD="bgcolor: #FAFAFA"]False[/TD]
[/TR]
</tbody>[/TABLE]


i have also put my table array into a table so the formula is updated as new data is added, as seen below

=VLOOKUP(RIGHT(E6,7)+0,PPD,17,0)

=IFERROR(VLOOKUP(RIGHT(E3,7)+0,PPD,17,0),"N/A")

 

[Special-K99]

=IF(C2="True",VLOOKUP(RIGHT(E3,7)+0,PPD,17,0),"N/A")

 

[Rasscal]

Perfect, thank you

 

[Rasscal]

to add a spanner in the works

my current formula is

=IF(AND(D2="PWG : Picked and packed wrong goods",Q2="Yes"),VLOOKUP(RIGHT(H2,7)+0,'FY18-FY19 PPD.xlsx'!PPD_3[#All],17,0),"Non WH Error")

& below is where i would like to get to

If cell D2 = PWG : Picked and packed wrong goods or DE : Despatch Error
and cell Q2 = Yes
Then lookup the right 7 numbers & display whats in column 17
IF
cell L3 Matches in column G

I hope that makes sense