If criteria is between 2 cells or matches either bring back data

[Jaffabfc]

HI,

The title probably isnt the best but basically i have a lot data on 2 seperate sheets.
1 sheet is showing in Column B an item code and in E a price F a start date G an end date
2nd sheet is showing Column A an item code and B a date.

So basically in column C in 2nd sheet i am wanting a formula to see if cell A matches column B in the 1st sheet and column B in sheet1 matches or is inbetween Coulnd F and G in sheet1 then bring back the price in sheet1.

Hope ive explained it well enough.

 

[etaf]

i think this is what you are after
=IFERROR(LOOKUP(1E+307,1/((Sheet1!A2=Sheet2!B:B)*(Sheet1!B2>=Sheet2!F:F)*(Sheet1!B2<=Sheet2!G:G))*Sheet2!E:E),0)

rather than use full column references , you maybe better off using a range

Book3
	A	B	C
1
2	a	5/12/23	500
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IFERROR(LOOKUP(1E+307,1/((Sheet1!A2=Sheet2!B:B)*(Sheet1!B2>=Sheet2!F:F)*(Sheet1!B2<=Sheet2!G:G))*Sheet2!E:E),0)

Book3
	B	C	D	E	F	G	H
1	item
2	a			100	1/1/23	1/31/23
3	b			200	2/1/23	2/28/23
4	a			300	3/1/23	3/31/23
5	b			400	4/1/23	4/30/23
6	a			500	5/1/23	5/31/23
7	b			600	6/1/23	6/30/23
Sheet2

Cell Formulas
Range	Formula
G2:G7	G2	=EOMONTH(F2,0)

i only leave the file on dropbox for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Jaffabfc]

Thanks for this.

I think its worked, had to change a few cells with me moving data around but once its calculated i will let you know.

Over 200,000 lines of data one sheet and over 400,000 on another.

thanks

 

[etaf]

ok,
you are welcome

 

[Jaffabfc]

Yes that seems to have worked thankyou

 

[etaf]

you are welcome