Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two sets of data in excel. One contains a set of data which represents the initial step of a process. The other set of data represents the additional steps which take place after the first step is completed. Each of the data records in the "additional step data" has an id in a column. I need to find the identifying codes of the "additional step data" which correspond with the initial step data records.

The problem is that I have to match the data in 4 columns between the two data sets and return the id of the "additional step data".

I started by doing a combination of an if and vlookup functions, but I got stuck when I tried to figure out how to get the if statement to reference the id of the matching "additional step data".

Basically I am trying to avoid having to search by manually filtering between two sets of data and finding corresponding records.

Does anyone have any idea about how to do this?

share|improve this question

migrated from Mar 24 '10 at 12:13

This question came from our site for professional and enthusiast programmers.

Hi EMJ - this question is better suited to – Otaku Mar 22 '10 at 20:33
can you post some sample data. I.e. two rows (and headers) from each table, what you are searching for, and the expected result? – guitarthrower Mar 22 '10 at 21:15
This sounds doable, but a data sample would make it easier to give you a usable answer. – Sux2Lose Mar 24 '10 at 14:14

This sounds like Pivot Table territory.

share|improve this answer

Sounds to me like your best bet will be to concatenate the four fields in both tables (in an extra "helper" column in both cases), then use this as a unique identifier as the basis of a lookup. So a formula such as:

=A2 & "-" & B2 & "-" & F2 & "-" & M2

(or whatever columns) would give a single value with these items strung together, separated by - characters for readability (drop this if you really don't care: A2&B2&F2&M2)

The you could use a MATCH(value_from_table1, range_of_new_column_in_Table2,0) to find the row, and an INDEX to bring back the ID from that row.

Hope this helps

share|improve this answer

You must log in to answer this question.