How build 4th "target" col based on earlier values compared to 3 cols?

dataguy · July 23, 2019, 1:02pm

I want to generate a “target” column based upon values in another 3 columns.
The existing completed columns are “driver”, “Abr1”, “drive2”.
Where “driver” and “drive2” are the same, use “Abr1” value in “target”.
Where they differ, select the value in earlier “Abr1” that corresponds to “drive2” in the same row.
In my hand constructed “target” here, the first 3 rows are identical to “Abr1” because “driver” and “drive2” match.
Then the algorithm uses the value in “drive2” to select a prior corresponding value in “Abr1” for “target” down the column.

Here are the columns:
image:!(/home/jim/Downloads/Pajek_Data/Screenshot_20190723_052400.jpeg “example”)

I have had interesting experiences trying VLOOKUP, INDEX with MATCH, etc. but no success yet. I am resisting writing a custom program as I prefer a standard Calc method I can adapt for future similar tasks.

erAck · July 23, 2019, 3:31pm

Please try to not upload screenshots but a small sample document instead. Use the paperclip button for attachments. Thanks.

dataguy · July 23, 2019, 1:07pm

Example.ods

The first 100+ rows of the actual data are easy because I can count on driver and drive3 being equal.
It is the following 4000+ rows that are the challenge.

Here is code that fails in the miniature table after the first 3 rows.
=IF(B2=D2,C2,INDEX(C2:C11,MATCH(D2,$D$2:D2-1,4)))

I am proceeding to modify that and also considering VLOOKUP.
It is my first experience using INDEX, MATCH, VLOOKUP. I hope they are adequate after I climb the first stages of the learning curve, but if you think I should try something else, please suggest it.

The solution was to use a combination of IF and VLOOKUP as here:
=IF(B2=D2,C2;VLOOKUP(D2,$B$2:$C$4,2,0))
I scaled that solution up to the bigger table and it works.

erAck · July 23, 2019, 3:27pm

This is not an answer, please edit your question instead. Thanks.

dataguy · July 23, 2019, 5:52pm

How do I insert the image?

mikekaganski · July 25, 2019, 3:35am

The task is incomplete.

What needs to be in D when there’s no match for current C is not found among matching pairs (A,C) above?
Which occurrence needs to be chosen if there are more than one match above? E.g., there could be rows “A;aaa;A” and “A;bbb;A” above; so which (aaa or bbb) should go to D in the third row “K;fff;A”?

The formula =IF(B2=D2,C2;VLOOKUP(D2,$B$2:$C$4,2,0)) cannot solve the problem as described in the question, and shown in the attachment in the answer above.