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

Ask Your Question

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

asked 2019-07-23 15:02:13 +0200

dataguy gravatar image

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.

edit retag flag offensive close merge delete


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

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

1 Answer

Sort by » oldest newest most voted

answered 2019-07-23 15:07:20 +0200

dataguy gravatar image

updated 2019-07-24 07:14:06 +0200


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.

edit flag offensive delete link more


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

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

How do I insert the image?

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

The task is incomplete.

  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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-07-25 05:35:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-07-23 15:02:13 +0200

Seen: 53 times

Last updated: Jul 24 '19