Match certain text in a cell and return the figure in the corresponding cell

[Goddess]

Hi,

I have a set of data in Sheet 1, as shown in table A3:B7. In Sheet 2, I need to find the corresponding country and match the amount found in Sheet 1, and insert the amount in column F. Vlookup doesn't work. Can advise what formula to use?

Book2
	A	B	C	D	E	F
1	Sheet 1				Sheet 2
2
3	Country	Amount			Location	Amount
4	UK	6			London - UK	6
5	USA	8			Torquay - UK	6
6	Germany	2			Heidelberg - Germany	2
7	Singapore	3			Singapore - Singapore	3
8					Munich - Germany	2
9					Manchester - UK	6
10
Sheet1

Thanks!

 

[Phuoc]

Try this

Book1
	A	B	C	D	E	F	G
1	Sheet 1				Sheet 2
2
3	Country	Amount			Location	Amount	Amount
4	UK	6			London - UK	6	6
5	USA	8			Torquay - UK	6	6
6	Germany	2			Heidelberg - Germany	2	2
7	Singapore	3			Singapore - Singapore	3	3
8					Munich - Germany	2	2
9					Manchester - UK	6	6
Sheet1

Cell Formulas
Range	Formula
G4:G9	G4	=LOOKUP(0,-FIND($A$4:$A$7,E4),$B$4:$B$7)

 

[Phuoc]

Or try

Book1
	A	B	C	D	E	F	G
1	Sheet 1				Sheet 2
2
3	Country	Amount			Location	Amount	Amount
4	UK	6			London - UK	6	6
5	USA	8			Torquay - UK	6	6
6	Germany	2			Heidelberg - Germany	2	2
7	Singapore	3			Singapore - Singapore	3	3
8					Munich - Germany	2	2
9					Manchester - UK	6	6
Sheet1

Cell Formulas
Range	Formula
G4:G9	G4	=SUMIF($A$4:$A$7,REPLACE(E4,1,FIND("-",E4)+1,""),$B$4:$B$7)

 

[Goddess]

=LOOKUP(0,-FIND($A$4:$A$7,E4),$B$4:$B$7)

Try this

Book1
	A	B	C	D	E	F	G
1	Sheet 1				Sheet 2
2
3	Country	Amount			Location	Amount	Amount
4	UK	6			London - UK	6	6
5	USA	8			Torquay - UK	6	6
6	Germany	2			Heidelberg - Germany	2	2
7	Singapore	3			Singapore - Singapore	3	3
8					Munich - Germany	2	2
9					Manchester - UK	6	6
Sheet1

Cell Formulas
Range	Formula
G4:G9	G4	=LOOKUP(0,-FIND($A$4:$A$7,E4),$B$4:$B$7)

Thanks!!