Reference a cells content, based on another cells content, that may move

English
#1

As per the title, I want to be able to ‘target’ a particular cells content, based on the content of the adjacent cell. Here is a very simple example, to make it easier for me to explain.

Say I have a column of cells, each cell having the date of the first of the month. So A1 has 01 Jan 2025, A2 has 01 Feb 2025, A3 has 01 Mar 2025 etc. all the way down to 01 Jan 2026.

Adjacent to each column A cell is a column B one containing a currency amount.

It’s easy for me to reference any of the currency cells but what if they move? What if I insert one or more rows, each new row having a new date and currency amount. For example, say I add a row below the 01 Jan 2025, one which has 12 Jan 2025 in the A column cell and a currency amount in the B column cell. My reference to, say B2 would then be pointing to the wrong cell.

So, is there a way to “select the value in the cell to the right of the one containing 01 Feb 2025”. IN that way it wouldn’t matter if the targeted cell moved.

My application is a bit more complicated than that but the answer will get me started. Thanks

#2

That is VLOOKUP.
.

Try it. A usual relative address should be adapted. A bit more complicated are ranges like A1:A5. If you insert between 2 and 3 the range will extend, but if you just fill A6 there is no automatic detection “this belongs to above range”.

#3

Thanks. I’ve have looked at VLOOKUP and some examples. However, they don’t show how to find a cell showing a particular value. For example:

Step 1: Enter the product code in cell A2.
Step 2: In cell B2, enter the following formula:
=VLOOKUP(A2,B2:E21,3,FALSE)
A2 is the lookup value.
B2:E21 is the table range.
3 is the column index number.
FALSE specifies that you want to perform an exact match.

Using the above example, I would want the lookup value to find a cell containing a certain date, then note the value of the cell to its right.

#4

and this is the number you seek.

Your “table” starts in A and you only told of column B. Lets assume 100 rows, so to find something in this range try

=VLOOKUP(A2,$A$2:$B$100,2,FALSE)

but usually you will not use a2 for this as this would mean, you already know =b2is your answer.

Just look again:
https://wiki.documentfoundation.org/Documentation/Calc_Functions/VLOOKUP

#5

Perfect. That link gives excellent advice - much better than the info I found earlier. OK, I now have example code that works and that I can modify to suit my exact needs:

=VLOOKUP(“01-Feb-25”, $B$11:$E$30, 4, 0)

I’ve also worked out how to modify that to target cells on a different Sheet (in the following example one called ‘Interest’.

=VLOOKUP(“01-Feb-25”,Interest.$B$11:$E$30,4,0)

Seems to work fine. Thanks for the help with this.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.