Cell value from Sheet 1 identical with cell value from Sheet 2

user_18460664 0
Viewed 60

So, the thing is: I have an excel with two sheets. In sheet 1 I have, let's say, 4000 senteces (arranged in some defined order).

Example

In sheet 2 I have those senteces on a single column randomnly arranged

Example.

On the column right to those sentences from sheet 2 I write the equivalent sentences in my native language and I don't know how to make all those senteces from Sheet 2, Column 1 as linked with the identical cell value from Sheet 1. I will attach an exemple so I can explain this better.

Example.

Can I do something about it? I don't know very much about VBA or excel commands so any help will be appreciated.

If I didn't explain my issue in a way so you can understand, please let me know.

Thank you!

excel-formula excel
1 Answers

In your Sheet2, you may use HYPERLINK combined with MATCH. As example:

enter image description here

My formula in Sheet2, cell B1 is:

=HYPERLINK("#Sheet1!C"&MATCH(A1;Sheet1!C:C;0))

If I click in that cell, it takes me to Sheet1, cell C9, where my target_value has been located.

Source: How to add a hyperlink to another worksheet

Please, notice that I manually typed the worksheet name, with a # symbol at left "#Sheet1!C"

UPDATE: If you have 5 target columns, you can combine IFERROR to search in 1 or another column:

enter image description here

As you can see, each value searched is on a different column. Now formula is:

=HYPERLINK(IFERROR("#Sheet1!C"&MATCH(A1;Sheet1!C:C;0);IFERROR("#Sheet1!D"&MATCH(A1;Sheet1!D:D;0);IFERROR("#Sheet1!E"&MATCH(A1;Sheet1!E:E;0);IFERROR("#Sheet1!F"&MATCH(A1;Sheet1!F:F;0);"#Sheet1!G"&MATCH(A1;Sheet1!G:G;0))))))

Anyways, I've uploaded a sample to Gdrive in case you want to check the formulas:

https://docs.google.com/spreadsheets/d/1JjEGno66vRFI2DfEIRLfG3bgHaQwpS4c/edit?usp=sharing&ouid=114417674018837700466&rtpof=true&sd=true

user_91998280
Related