Return cell reference as result of if statement with vlookups

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?

EMJ

Posted 2010-03-22T20:27:54.190

Reputation:

Hi EMJ - this question is better suited to http://superuser.com.

– None – 2010-03-22T20:33:02.263

1can you post some sample data. I.e. two rows (and headers) from each table, what you are searching for, and the expected result? – guitarthrower – 2010-03-22T21:15:11.877

This sounds doable, but a data sample would make it easier to give you a usable answer. – Sux2Lose – 2010-03-24T14:14:42.077

Answers

This sounds like Pivot Table territory.

Shevek

Posted 2010-03-22T20:27:54.190

Reputation: 15 408

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

AdamV

Posted 2010-03-22T20:27:54.190

Reputation: 5 011