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

#### Goddess

##### Board Regular
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
ABCDEF
1Sheet 1Sheet 2
2
3CountryAmountLocationAmount
4UK6London - UK6
5USA8Torquay - UK6
6Germany2Heidelberg - Germany2
7Singapore3Singapore - Singapore3
8Munich - Germany2
9Manchester - UK6
10
Sheet1

Thanks!

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Try this

Book1
ABCDEFG
1Sheet 1Sheet 2
2
3CountryAmountLocationAmountAmount
4UK6London - UK66
5USA8Torquay - UK66
6Germany2Heidelberg - Germany22
7Singapore3Singapore - Singapore33
8Munich - Germany22
9Manchester - UK66
Sheet1
Cell Formulas
RangeFormula
G4:G9G4=LOOKUP(0,-FIND(\$A\$4:\$A\$7,E4),\$B\$4:\$B\$7)

Or try

Book1
ABCDEFG
1Sheet 1Sheet 2
2
3CountryAmountLocationAmountAmount
4UK6London - UK66
5USA8Torquay - UK66
6Germany2Heidelberg - Germany22
7Singapore3Singapore - Singapore33
8Munich - Germany22
9Manchester - UK66
Sheet1
Cell Formulas
RangeFormula
G4:G9G4=SUMIF(\$A\$4:\$A\$7,REPLACE(E4,1,FIND("-",E4)+1,""),\$B\$4:\$B\$7)

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

Book1
ABCDEFG
1Sheet 1Sheet 2
2
3CountryAmountLocationAmountAmount
4UK6London - UK66
5USA8Torquay - UK66
6Germany2Heidelberg - Germany22
7Singapore3Singapore - Singapore33
8Munich - Germany22
9Manchester - UK66
Sheet1
Cell Formulas
RangeFormula
G4:G9G4=LOOKUP(0,-FIND(\$A\$4:\$A\$7,E4),\$B\$4:\$B\$7)
Thanks!!

Replies
0
Views
144
Replies
3
Views
105
Replies
1
Views
95
Replies
4
Views
276
Replies
4
Views
295

1,203,146
Messages
6,053,755
Members
444,681
Latest member

### 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.

### Which adblocker are you using?

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

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