CODEWITHSUNDEEP
excel
sam
sam

Reputation: 253

Fill value based on another columns in Excel

I have the following columns in excel, i want to change the ID column to New ID based on Match ID, for example on the ID column first data is Zrecruit_376840000000415007 which is Zrecruit_376840000000415007 = 5789 in Match ID and New ID column the ID should be replaced to 5789.

ID                              Match ID                         New ID
Zrecruit_376840000000415007     Zrecruit_376840000000367020  =   5782
Zrecruit_376840000000215296     Zrecruit_376840000000375036  =   5783
Zrecruit_376840000000217195     Zrecruit_376840000000389001  =   5784
Zrecruit_376840000000218111     Zrecruit_376840000000369168  =   5785
Zrecruit_376840000000219665     Zrecruit_376840000000392001  =   5786
Zrecruit_376840000000222030     Zrecruit_376840000000410013  =   5787
Zrecruit_376840000000226008     Zrecruit_376840000000432013  =   5788
Zrecruit_376840000000227079     Zrecruit_376840000000415007  =   5789

I have applied the formula as attached in the image but im getting N/A

Upvotes: 0

Views: 2960

Answers (2)

Maddy
Maddy

Reputation: 781

Try this

=REPLACE(VLOOKUP(A2 & "*",$B$2:$B$9,1,0),1,FIND("=",VLOOKUP(A2 & "*",$B$2:$B$9,1,0),1)+3,"") Here “+3” denotes Three spaces after “=” Char. Change this as per your requirement

ID = Column A Matched ID = Column B New ID = Column C

Paste this formula in column C

enter image description here

Upvotes: 1

Pratham
Pratham

Reputation: 1713

You can write a formulae to find the row number for the match and get its corresponding cell value, something similar to below

=INDIRECT(ADDRESS(MATCH(A2,B2:B9,0),3))

This formulae :

  • Find the Matching text in A2 in range B2:B9
  • Gets the address for that row with column as 3 (or Column C)
  • Gets the value of that address using INDIRECT() function

This will give you the values in the newer column then you can copy paste it where you want,

Or you can write similar function in the VBA script to replace the value directly

Upvotes: 0

Related Questions