Cross Workbook equation to get data into another format (dealing with duplicate numbers)

[katie97]

I have an IF statement pulling date onto Sheet 1 (=IF('Sheet 2'!$E$4=H15,'Sheet2'!$C$4,False). However, I have some numbers in the E column that are identical, while the respective values in row C are different. Is there a way to tell excel to move onto the next instance of the number in column E? For example, if it has already pulled C4 for E4, can I tell it to move on to E5:$E to search for the next occurrence and then pull the respective number from column C?

I realize an IF statement may not be best for this, but I'm at the end of my excel knowledge, so any help would be greatly appreciated!

Link to screenshots: https://drive.google.com/drive/folders/15gkI3-eED9mesOvanVcNLZiFjYWWuzSU?usp=sharing

 

[Sam_D_Ben]

I guess you are looking for this.

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	LINE#		LINE		PO#			PO Number
2	111		111		2019-40			2019-40
3	112		112		2019-40
Sheet2

Cell Formulas
Range	Formula
A2	=IF($E2=$H$2,$C2,FALSE)

 

[Peter_SSs]

Welcome to the MrExcel board!

Try these

In cell B34 of the formula sheet:

=IFERROR(INDEX('PO Log'!C$4:C$100,AGGREGATE(15,6,(ROW('PO Log'!E$4:E$100)-ROW('PO Log'!E$4)+1)/('PO Log'!$E$4:$E$100=$H$15),$A34)),"")

then, after adjusting the $100s for the end of your data (if required) copy/paste this formula to B36, B38 etc


In cell E34 of the formula sheet

=IF($B34="","",INDEX('PO Log'!B$4:B$100,AGGREGATE(15,6,(ROW('PO Log'!B$4:B$100)-ROW('PO Log'!B$4)+1)/('PO Log'!$E$4:$E$100=$H$15),$A34))&"")

adjust & copy to rows 36, 38, ...

You should then be able to copy the E34 formula to Z34 & try adjusting it to point to the correct column. Post back if you need more help with that part or the above.

BTW, it looks like you might be using a Mac version of Excel. If so, always a good idea to state that & your operating system as the majority of helpers (including me) will normally assume PC versions.