Vlookup query

A Zed

New Member
Joined
Jul 23, 2007
Messages
21
Hi People, just found this invaluable website, and need to post already.

Ok in simple terms I have a sheet with
surname, first name, date of birth, amount, amount, amount.

a second sheet with

surname, date of birth, amount

need to get amount from column 6 in first sheet into column 3 in second sheet, but only if surname and date of birth BOTH match. (mulitple people with same surname)

Id prefer to not merge the two columns, as the resulting sheet actually has to be uploaded into another program and format/layout can not be changed.
Have just overloaded my brain looking at other peoples overdone formulae and require assistance.

I figure it must be simple and doable, as Ive had need for it many times.

Any assistance greatly appreciated.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Hi People, just found this invaluable website, and need to post already.

Ok in simple terms I have a sheet with
surname, first name, date of birth, amount, amount, amount.

a second sheet with

surname, date of birth, amount

need to get amount from column 6 in first sheet into column 3 in second sheet, but only if surname and date of birth BOTH match. (mulitple people with same surname)

Id prefer to not merge the two columns, as the resulting sheet actually has to be uploaded into another program and format/layout can not be changed.
Have just overloaded my brain looking at other peoples overdone formulae and require assistance.

I figure it must be simple and doable, as Ive had need for it many times.

Any assistance greatly appreciated.

On Sheet2 in C2:

Control+shift+enter...

=INDEX(Sheet1!$F$2:$F$100,MATCH(1,IF(Sheet1!$A$2:$A$100=A2,IF(Sheet1!$C$2:$C$100=B2,1)),0))
 

A Zed

New Member
Joined
Jul 23, 2007
Messages
21
Thanks for that, will give it a try.
Anyone else with other options?
 

Forum statistics

Threads
1,181,407
Messages
5,929,766
Members
436,687
Latest member
Glass of Gin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top