# Thread: IF - AND STATEMENT

1. I am trying to write a if formula where there are 2 conditions that must be met. I am attaching a sample worsheet to demonstrate. The reference schedule has all basic information. The query schedule is missing the HRS. and this is where the if statement should be. The if statement should look to the REFERENCE SCHEDULE and if the ID agrees and the AMOUNT agrees then the REFERENCE SCHEDULE's HRS. should be posted in the approriate QUERY SCHEDULE's HRS cell.

Thanks

2. Not sure what is the desired result.
Attached is a sample Table of Total Hours and Total Amounts by Dept and by ID.

Please note that Reference Sch. in Cells B22 and B27 contain two different versions of Dept ADE. B22 has ADE and B27 has ADE (space).

3. In H4 enter:
=LOOKUP(9.99E+305,CHOOSE({1,2},0,LOOKUP(2,1/((\$A\$4:\$A\$31=F4)*(\$B\$4:\$B\$31=G4)),\$C\$4:\$C\$31)))
and copy down, if I understood correctly.

4. Are you saying an IF formula wth an AND statement will not work?

I am hoping the results will be an HRS amount which fulfills both the ID & the AMOUNT criteria. Is this possible to achieve? If so, what is the formula.

Thank you.

5. Still trying to figure out what is wanted for HRS on QUERY SCHEDULE.
Please supply the correct HRS that should show on QUERY SCHEDULE?

QUERY SCHEDULE contains AMT's that don't exist on REFERENCE SCHEDULE
For example -
these AMT's from QUERY SCHEDULE do not exist on REFERENCE SCHEDULE .....
8,300.00 5,944.00 1,233.00 1,200.00

Please be clearer on how HRS should be calculated.

6. Sorry for not being clear enough in my text.

The objective is to have the QUERY SCHEDULE record the hours from the REFERENCE SCHEDULE if there is a match with the ID & the AMOUNT on the REFERENCE SCHEDULE.

If the REFERENCE SCHEDULE matches the ID but not the AMOUNT or the AMOUNT but not the ID then no hours will be recorded in the QUERY SCHEDULE and those cells shall remain blank.

I am attaching the worksheet with the HRS recorded in the QUERY

7. Ooops pulled trigger too fast!

Sorry for not being clear enough in my text.

The objective is to have the QUERY SCHEDULE record the hours from the REFERENCE SCHEDULE if there is a match with the ID & the AMOUNT on the REFERENCE SCHEDULE.

If the REFERENCE SCHEDULE matches the ID but not the AMOUNT or the AMOUNT but not the ID then no hours will be recorded in the QUERY SCHEDULE and those cells shall remain blank.

I am attaching the worksheet with the HRS recorded in the QUERY Schedule

Again, sorry for not being concise

8. Hi - I am still confused here. The first one I checked does not ring a bell.
How does Cell H5 become 16? What matches were used to get a 16 in Cell H5?

Thanks

9. The Hrs. cells in the Query Schedule will house the formula. The formula will look to the Reference Schedule and see if there is an exact match to both the ID and the AMOUNT in the same row. If there is then the Hrs. are to be recorded in the Query Schedule. there may not be a match for each set of ID and AMOUNT in the Query Schedule from the Reference schedule

So the formula should translate to " If in the Query Schedule the ID & Amount in a row agrees to the ID & Amount in the Reference Schedule then record the HRS from the agreed record in the Reference Schedule in the HRS cell in the Query Schedule.

I fixed the w/s. and attached

Thanks

10. Did you want "" or 0 returned where there is no match?

11. A blank space would be good.

Thank you.

12. In that case:
=IF(ISNA(LOOKUP(2,1/((\$A\$4:\$A\$31=F4)*(\$D\$4:\$D\$31=I4)),\$C\$4:\$C\$31)),"", LOOKUP(2,1/((\$A\$4:\$A\$31=F4)*(\$D\$4:\$D\$31=I4)),\$C\$4:\$C\$31))
in H4 and copy down.

13. Originally Posted by rory
In that case:
=IF(ISNA(LOOKUP(2,1/((\$A\$4:\$A\$31=F4)*(\$D\$4:\$D\$31=I4)),\$C\$4:\$C\$31)),"", LOOKUP(2,1/((\$A\$4:\$A\$31=F4)*(\$D\$4:\$D\$31=I4)),\$C\$4:\$C\$31))
in H4 and copy down.

Thank you very much.

What was the significance of the "=LOOKUP(9.99E+305,CHOOSE({1,2}" in the first formula?

14. I've adopted a slightly (or very) different approach. I envisaged a problem in the accuracy of your method in that rows #5 and #6 contained the same ID, AMT, and HRS, but a different DEPT.

CONCATENATE the ID etc. =CONCATENATE(A3,B3,D3)
In H4 and below =OFFSET(\$C\$2,MATCH(CONCATENATE(F3,G3,I3),\$E\$3:\$E\$2 9,0),)

It is of course very important to ensure that things like the errant space in cell G16 are corrected.

[attachment=88970:suggestion.xls]

15. Originally Posted by MNN
What was the significance of the "=LOOKUP(9.99E+305,CHOOSE({1,2}" in the first formula?
It was to return 0 if there was no match, instead of a #N/A error. That approach would not work to return "" though, so I altered it to use ISNA() instead.
PS If you wanted to add a department check into the formula too, that's easy enough.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•