How to get the last value out of multiple values for a cell from another workbook?

[Ammar Qadir]

I have a main sheet (book1) which is master sheet of all products and second sheet (book2) has multiple values for a particular product. Product name is common and unique. I want to get the last entry (value ) of a product from 2nd workbook.

 

[Habtest]

Try this and adjust to your actual range:

Book3
	A	B	C	D	E
1	Ops	Status		Ops	Status
2	ABC	F		ABC	A
3	DEF	E		ABC	B
4	XYZ	H		ABC	C
5				DEF	D
6				DEF	E
7				ABC	F
8				XYZ	G
9				XYZ	H
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LOOKUP(2,1/($D$2:$D$9=A2),$E$2:$E$9)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Ammar Qadir]

Try this and adjust to your actual range:

Book3
	A	B	C	D	E
1	Ops	Status		Ops	Status
2	ABC	F		ABC	A
3	DEF	E		ABC	B
4	XYZ	H		ABC	C
5				DEF	D
6				DEF	E
7				ABC	F
8				XYZ	G
9				XYZ	H
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LOOKUP(2,1/($D$2:$D$9=A2),$E$2:$E$9)

Thank you for your reply. Yes it works with same sheet. I want to do the same operation but from two different workbooks. I could not get it working for two different workbooks. really appreciate your help.

 

[Habtest]

I understand it should work even if source data and summary sheet are in another workbooks. If they are open at the same time, the values will update automatically; If the source file is closed, you may update the summary sheet by update when opening it or Data - Edit Links - Update Values.
Can you specify the issue? And also update account details would be helpful.