Libre Office: Dragging down VLOOKUP function

[mmmmmmmmmmmmmmmmmmmm]

Dear all,

I have an excel with several sheets. All the sheets have the same structure but different data inside.
I have a MIN function that looks for the minimum value for the J3 cell in the different sheets.
What I want to do is find the name of the cell that contains this minimum value.

The cell J3 contains the minimum number I am looking for, the K3 contains irrelevant information, and the L3 cell contains the name of the sheet too.

Up until now i have tried with:
=VLOOKUP(J3;INDIRECT("'"&$J$39:$J$53&"'!J3:L3");3;0)

Where J39-J53 is a list containing the names of my sheets.

This works just fine.
Now, I try to drag this cell to get the exact same result with the following cells.

I would like to get a result like:

=VLOOKUP(J4;INDIRECT("'"&$J$39:$J$53&"'!J4:L4");3;0)

But all I get is exactly the same formula as the first one.

I am an Excel noob, and I am stuck with thism, could anyone help me?

[AliGW]

YOu have posted in the VBA section - I shall move this to the correct section.

[63falcondude]

J3:L3 are in quotes so they will not change as you drag the formula down.

Maybe you can do something like this:

=VLOOKUP(J3;INDIRECT("'"&$J$39:$J$53&"'!J"&ROW(J3)&":L"&ROW(L3));3;0)

This is untested (as I don't have a sample to test on in front of me).

[mmmmmmmmmmmmmmmmmmmm]

Dear 63falcondude,

Thank you for your comment.
I tried the formula you suggested but I still could not drag down the funcion and get the numbers to autoincrement.

Could it possibly be that I am using LibreOffice version 6.2.5.3 instead of Microsoft Office?

I am trying to attach a file with similar data of what I am working with for testing purposes, but I cannot do it.
The attachment engine is not working for me.
If you send me your email I will forward this file to you.

[AliGW]

Could it possibly be that I am using LibreOffice version 6.2.5.3 instead of Microsoft Office?

This is an interesting disclosure. You have posted in the Excel Functions & Formulas section (moved from the Excel VBA section) - I shall move this again to Other Platforms where it should have been posted in the first instance.

[mmmmmmmmmmmmmmmmmmmm]

Dear all,

After a few attempts, I got the thing partially working.

What I did was, I used 63falcondude formula (THANKS!) for the first row (J3 and so on).
Then I copied it to the second row and manually changed the desired parameters (I incremented the numbers myself).
So I also obtained the result that I wanted.
Then I dragged these two down and I got the result wanted.

Up until now, good.

My problem now is: this does not work for other columns.
If I want to use the same formula with columns O-P-Q instead of J-K-L, it does not work.
What happens is that even if I change the J-K-L values for O-P-Q ones, it still "searches" on the old ones.

Could anyone help me with that?

Thanks a lot.

[AliGW]

What is the formula that you are using successfully on column J?

[mmmmmmmmmmmmmmmmmmmm]

Sorry, maybe I didn't explain myself properly.

In column L I am applying the formula that 63falcondude suggested, which is:

=VLOOKUP(J3;INDIRECT("'"&$J$39:$J$53&"'!J"&ROW(J3)&":L"&ROW(L3));3;0)

and this formula is based on information on cell J3, and J3 and L3 for other sheets.

[AliGW]

OK - show us what you did to change it for columns O-P-Q.

[mmmmmmmmmmmmmmmmmmmm]

=vlookup(o3;indirect("'"&$j$39:$j$53&"'!j"&row(o3)&":l"&row(q3));3;0)

[rorya]

You need to change these bits:

=vlookup(o3;indirect("'"&$j$39:$j$53&"'!j"&row(o3)&":l"&row(q3));3;0)

[Pete_UK]

You have not changed the references to j and l in the formula. Try this:

=vlookup(o3;indirect("'"&$j$39:$j$53&"'!o"&row(o3)&":q"&row(q3));3;0)

Hope this helps.

Pete

[mmmmmmmmmmmmmmmmmmmm]

Thank you so much!!!
That works now.

Rookie mistake.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[mmmmmmmmmmmmmmmmmmmm]

Done.
Thank you all.