vlookups

JGarza

Board Regular
Joined
Mar 26, 2002
Messages
93
I have two spreadsheets. Each have two columns of information that match. One of the spreadsheets has some information that I want to bring over. How can I do that?

For example:
SPREADSHEET 1
A B
123 AC
123 AD
578 AQ
578 AS
578 AT
123 AE

SPREADSHEET 2
A B C
123 AD 255
578 AQ 658

I want a vlookup in spreadsheet 1 that will look up the "123" "AD" in spreadsheet 2 and bring back the "255".

HELP
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
One of a few methods would be to insert a new column before column C and concatenate the lookup values on both spreadsheets.

e.g. =CONCATENATE(A2,B2)

then do the vlookup off the concatenated ranges.

=VLOOKUP(C2,C2:D100,2,0)

Cheers,
Jon
 
Upvote 0
To use vlookup(), you would need to concatenate your "123" and "AD" entries in both sheets, and use this value as the vlookup source.
 
Upvote 0
If the combinations are truly unique you could use eg:

=SUMPRODUCT(--(Sheet2!A$1:A$100=A1),--(Sheet2!B$1:B$100=B1),Sheet2!C$1:C$100)

Otherwise, to find the first occurrence you could use, eg:

=INDEX(Sheet2!C$1:C$100,MATCH(A1&B1,Sheet2!A$1:A$100&Sheet2!B$1:B$100,FALSE))

which must be confirmed with Ctrl+Shift+Enter, not just Enter.
 
Upvote 0

Forum statistics

Threads
1,226,504
Messages
6,191,431
Members
453,657
Latest member
DukeJester

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
Back
Top