Lookup using multiple columns

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70
Hi. In my workbook, I have sheet "Times" with the column headings Nomination, Code, House, Year, Gender, Event. All columns have entries and there are about 1400 rows.

In sheet "Field" I have the same columns. All columns are filled except Code.

I need to look in sheet "Field" and "remember" the entries across a row in all columns except Code.

Then I need to check in sheet "Times", work its way down the array until it finds the same unique match of entries. When it finds that, it needs to grab the corresonding entry in the Code column and return that entry in the Code column of sheet "Field".

I really hope this makes sense. What function do I need? I suppose it's a bit like a lookup with more than one column to check.

Thanks in advance for help with this one.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
You should use a Vlookup, but concatenate the columns that you are looking up into one field.

For example, imagine you had an address to find. In one sheet there is house number, address line 1, address line 2 etc...

you could put a formula in the adjacent cell to the address details, concatenating the values using the "&" symbol. You can then look up this single value by concatenating the value you are looking up .

Does this make sense to you?
 

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70
patrickmuldoon99

thanks heaps for your ultra-quick reply. yes it makes perfect sense, I tried it and it works wonderfully.

Cheers!
 

Forum statistics

Threads
1,136,267
Messages
5,674,730
Members
419,523
Latest member
Urnovio

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