CODEWITHSUNDEEP
excelindexingmatching
mozza54
mozza54

Reputation: 21

Match 2 columns in excel to show value in a 3rd column

I've got an excel spreadsheet with 2 tabs on, the first tab has a location, location type and version number column. The second tab has the location, location type and a blank column I want to work on.

On both tabs, the location is in there multiple times (otherwise it would be a lot simpler) and Im only working with one specific location type which hopefully will make this easier but on my main sheet, for each location there are 3 location types.

What I want to do is put the value from the version number field into my blank cell where the location and version number match up.. is it possible

Tab 1 - Location info

Location, location type, Version
LEEDS      Live          1.0.01
LEEDS      Test          1.1.01
LEEDS      Train         1.2.02

Tab 2 - issues

Location, Location type, Version
LEEDS, Live, (HERES WHERE I WANT THE VERSION BASED ON MATCHING A and B)
LEEDS, Live, (HERES WHERE I WANT THE VERSION BASED ON MATCHING A and B)
LEEDS, Live, (HERES WHERE I WANT THE VERSION BASED ON MATCHING A and B)

Theres a lot of locations but thats pretty much it.

Upvotes: 2

Views: 7129

Answers (1)

enderland
enderland

Reputation: 14145

You can do this using the following formula (without adding values to your table)

{=INDEX(Sheet1!C:C,MATCH(A2,IF(Sheet1!B:B=B2,A:A),0))}

The sheetnames might not be exactly what you are looking for - this assumes your lookup data in Tab2 has A2 = "LEEDS" and B2 = "Live".

You will need to make these formulas (if in a list) array formulas by using control+shift+enter (on Mac substitute command for control). This makes the formula work as an array operation. More information on array formulas.

However, one of the things I have done in these sorts of situations is make a "key" value on my main data tab which is unique. Then, instead of having to do a complicated formula (which really is more for when the spreadsheet will be maintained beyond a few days and either myself or someone else will need it) I can use a simple vlookup - which is much easier to understand than a combination of MATCH etc.

So what I would do is add a column in A for Tab 1 which is called "Key" and make this value something like:

= B2 & B3

This gives a unique key you can reference each row using.

Then, on your second tab, you can do something like

=vlookup(A2 & A3, Tab1SheetName!A:D, 4, false)

This is considerably easier to understand when you look back at this spreadsheet in several weeks/months or someone else needs to use it.

Upvotes: 1

Related Questions