CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulasubstitution
user4042721
user4042721

Reputation:

Substituting cell values in Excel based on matching criteria

I'm not sure is my Title correct, but I don't know how else to put it. Not an Excel expert so I would need help here.
I'm having COL A and COL B, COL A containing a name and COL B containing a link to COL A. All values in COL A and COL B are unique.

Then I have COLUMNS C, D, E, F, G, H containing values from COL A.

I need to replace each value in COLUMNS C, D, E, F, G, H with the value from COLUMN B (link).

Maybe a screenshot will explain it better. enter image description here I really don't know how to do that, so straight to the point answer would be awesome. I'm working with Google Spreadsheets and Excel, but I don't know how to create macros, so solution with formulas would be appreciated.

Upvotes: 0

Views: 59

Answers (1)

UK97
UK97

Reputation: 248

Im not sure how to replace the value in the Column C,D...H but you can get the values in corresponding columns I, J...N

Step 1: Enter the follwing formula in Cell I1

=INDIRECT((ADDRESS(MATCH(C1,$A:$A,0),2,3,1)))

Step 2: Copy the formula to all cell in Column I, J, K, L, M and N Note- The formula already has absolute and relative reference in it so there is no need to change the formula for each cell

Step 3: Copy Column I-N and use "Ctrl+Alt+V to paste in place of Columns C-H and select "Values" in the dialogue box and hit enter

You can leave out step 3 if you dont mind having a second set of links alone.

I'm not an expert in Excel but I believe my solution gets the job done although there may be more efficient ways out there.

Upvotes: 1

Related Questions