# not so simple formulas or explanations for that matter

#### Rasscal

##### New Member
I have two formulas with issues due to my own lack of excel knowledge

formula 1

=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

formula 2

im trying it count distinct values from a column in a table from another workbook if the values are between a certain date range(cell a1 & cell a2).

my current formula for the most part is

=SUM(--(FREQUENCY('FY18-FY19 PPD.xlsx'!\$B\$2:\$B\$51003,'FY18-FY19 PPD.xlsx'!\$B\$2:\$B\$51003)>0))

but how do i add if between date in cell a1 & b2?

any help
appreciated

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### James006

##### Well-known Member
Hello,

One question after the other ...

Formula 1 ...

You could test :

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

Hope this will help

#### Rasscal

##### New Member

Hello,

One question after the other ...

Formula 1 ...

You could test :

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

Hope this will help

below is an example of my data, your sugestion for the first will result "micky mouse" (correct by chance as its the first result) the second will result "bugs bunny" which is incorrect.

sorry this was my poor explaination

the result i would like to get

& below is where i would like to get to

If cell B = PWG : Picked and packed wrong goods
and cell Q2 = Yes
Then lookup the right 7 numbers of column A agaist workbook 2 column A & display whats in column D
IF cell of column B is PICK look at cell C & find the match of column D = display user
OR
If cell B = DE : Despatch error
and cell Q2 = Yes
Then lookup the right 7 numbers of column A agaist workbook 2 column A & display whats in column D
IF cell of column B is SHIP look at cell C & find the match of column D = display user

(so the result of E3 should be Daffy duck & E4 would be elmer fudd)

Workbook1

 A B C D E 1 ORDER NO REASON WAREHOUSE ERROR PART USER 2 TT1234567 PWG : PICKED WRONG GOODS YES ABC1 (RETURN RESULT HERE) 3 TT7654321 DE : DESPATCH ERROR YES ABC3 (RETURN RESULT HERE) 4 TT7654321 PWG : Picked WRONG GOODS YES ABC4 (RETURN RESULT HERE)

<tbody>
</tbody>

Workbook2

 A B C D SALES ORDER TASK PART USER 1234567 PICK ABC1 MICKY MOUSE 1234567 SHIP ABC1 DAFFY DUCK 7654321 PICK ABC2 BUGS BUNNY 7654321 PICK ABC3 BUGS BUNNY 7654321 PICK ABC4 ELMER FUDD 7654321 SHIP ABC2 MICKY MOUSE 7654321 SHIP ABC3 DAFFY DUCK 7654321 SHIP ABC4 BUGS BUNNY

<tbody>
</tbody>

Replies
4
Views
109
Replies
5
Views
116
Replies
9
Views
667
Replies
19
Views
311
Replies
6
Views
501

1,195,992
Messages
6,012,740
Members
441,724
Latest member
Aalbid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back