We will be migrating from Ask to Discourse on the first week of August, read the details here

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

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.

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.

edit retag close merge delete

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

( 2019-07-23 17:31:05 +0200 )edit

Sort by » oldest newest most voted

C:\fakepath\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.

more

( 2019-07-23 17:27:14 +0200 )edit

How do I insert the image?

( 2019-07-23 19:52:51 +0200 )edit

1. What needs to be in D when there's no match for current C is not found among matching pairs (A,C) above?
2. 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.

( 2019-07-25 05:35:39 +0200 )edit