Microsoft Excel 2007

I'm having difficulty figuring out a function for what I

want to do...The first two columns in my document coincide with

their respective values. Column A (File Number) is

coded for Participant Description (Column B) and the

values in each row are associated with one another...However, I have a 3rd column (Article Number) whose

values mean the same as the File Number column,

but the the rows don't match up.What I need is a fourth column. In this column I'd like

the value from the Participant Description column to

be imputed if the Article Number and File Number

column are the same.An example from the attached spread sheet.

A2 = 7, B2 = 6, C2 = 7 - I need D2 to equal B2

That is easy enough, but this is where I get lost...

A2 = 7, B2 = 6, C3 = 7 - I need D2 to equal B2Also,

If you look at the document, you'll see that that

Column A and Column C don't match up, but they

represent the same Document ID number... Column B

represent a category that corresponds to the

Document ID in Column A - Without writing 250 if

statements I would like to see if I can get the Value in

Column B (For a corresponding value match between

column A and C) imputed into Column D.So in this attached file... Column D represents the cell

whose value I want it to represent...Thanks in advance for your help on this.

Put this in D2 and drag it down. =OFFSET(INDIRECT("A"&MATCH(C2,$A:$A,0)),0,1)

MATCH(C2,$A:$A,0) will find the first occurrence of the value in C2 in Column A and return its position, which is 2.

INDIRECT("A" & 2) will "build" an address string by appending the result of the MATCH function with "A", evaluating to A2.

OFFSET(A2,0,1) will return the value in the cell that is OFFSET from A2 by 0 rows and 1 column, which is B2.

Here's another way that seems to work.

Put this formula in D2 and drag down.=LOOKUP(C2,$A$2:$A$420,$B$2:$B$420)

MIKE

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History