# "IFAND" help (I think!)

#### carefreeant

##### New Member
Hi,

Bit of a complicated one but hopefully it makes sense.

• To the left of my worksheet I have a list of items sold, and the price charged
• To the right of my sheet I have a price list. This shows the item, the start date of the price, and then the end date of the price

What I am basically try to write, is a formula that achieves the following:

• If the product code in cell B2
• Matches the product code in X2
• AND
• The date in A2
• Is greater than the date in Y2
• AND
• The Date in A2
• Is less than the date in Z2
• Then pull through the price in AA2

Could anybody assist with this please? I have tried various version of my base formula, but just cannot quite get it to achieve exactly what I am after.

Last edited:

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Hi there, you can try this:
<z2), aa2,="" "")

Edit: For some reason it's cutting off what I am typing...</z2),>

=IF(AND(B2=X2, A2>Y2, Z2>A2), AA2, "")

Edit2: There we go

Last edited:
Hi there, you can try this:
<z2), aa2,="" "")

Edit: For some reason it's cutting off what I am typing...</z2),>

=IF(AND(B2=X2, A2>Y2, Z2>A2), AA2, "")

Edit2: There we go

Hi,

Many thanks for this.

For some reason, however, I can only pull through blank cells. I have re-arranged my sheet slightly so that it is easier to read this end, and the formula reads as follows:

=IF(AND(B2=Q2,A2>S2,T2>A2),R2,"")

I have tried various versions of this but just cannot seem to pull anything through apart from a blank. Have I missed something?

it works for me, may be the date format for your cells are not the same

Book1
ABCDEFG
1
206/02/2019Prod1Prod130/01/201913/02/2019100100
Sheet1
Cell Formulas
RangeFormula
G2=IF(AND(B2=C2,A2D2),F2,"")

it works for me, may be the date format for your cells are not the same

ABCDEFG
1
206/02/2019Prod1Prod130/01/201913/02/2019100100

</tbody>
Sheet1

Worksheet Formulas
CellFormula
G2=IF(AND(B2=C2,A2<E2,A2>D2),F2,"")

</tbody>

<tbody>
</tbody>

Thanks.

I think I can see why we are still having trouble.

Our Sales Data (to the left of the sheet) run through over 3000 rows
The Price List to the right, is only across 20 rows (we are only looking at the prices of 20 products).

The formula being using would therefore need to point solely at the 20 rows of data in the Price List. We therefore need the formula to recognise these cells as a range I believe?

Hi,

If I understand correctly, this should do what you want.

Change/adjust cell references/range as needed, formula copied down:

Book1
ABCDXYZAA
1Sale dateProd codePriceProd codePrice startPrice endPrice
22/6/2019a123400b3451/28/20191/30/2019100
31/29/2019b345100d4561/29/20192/2/2019200
62/10/2019e789300v8952/2/20192/3/2019500
82/1/2019d456200
Sheet548
Cell Formulas
RangeFormula

Hi,

If I understand correctly, this should do what you want.

Change/adjust cell references/range as needed, formula copied down:

ABCDXYZAA
1Sale dateProd codePriceProd codePrice startPrice endPrice
22/6/2019a123400b3451/28/20191/30/2019100
31/29/2019b345100d4561/29/20192/2/2019200
62/10/2019e789300v8952/2/20192/3/2019500
82/1/2019d456200

</tbody>
Sheet548

Worksheet Formulas
CellFormula

</tbody>

<tbody>
</tbody>

Thank you for this - really appreciate it. It definitely seems to work (I have absolutely no idea why or how but it has solved the problem we are having, so I am really grateful.

Many thanks

You're welcome, welcome to the forum, thanks for the feedback.

A SUMIFS would also work.

and it's more forgiving of whole column references.

Replies
3
Views
102
Replies
1
Views
188
Replies
3
Views
305
Replies
11
Views
399
Replies
10
Views
171

1,202,987
Messages
6,052,932
Members
444,616
Latest member
novit19089

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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