I have implemented a drop-down menu which contains a long list of recipe names. By selecting a recipe, I need to retrieve its’ corresponding column number so I can access the list of information in that column. MATCH() returns a row number, not a column number, despite what the help file says. …any ideas on how to do this, short of transposing the whole array?
Please post an example file; it makes it easier to suggest something.
It should return neither row nor column. It should return a position in the provided range.
.
Please share your command and example data. Let’s see, if you found a bug …
What version of LibreOffice do you use?
1 Like
Looks like I’m on version 25.8.6.2 (X86_64)
For a drop–down menu, I look up one row from a table of recipes $‘Recipe Log’.$A$1:$ND$90, along row $L$6:$L$90.The drop-down menu works fine, and I select the desired recipe with it.
In a separate call, I need to know the column number so I can offset into the table to retrieve the recipe column of information. I tried all sorts of variations on MATCH(search criterion, lookup array, type), using the recipe name from the drop-down menu for the search criterion, the same row of recipe names for the lookup array, and using for type either a zero or 1 or leaving it out. the match always returned a numeric 1, which I guess means “row one of one”. …not helpful. If it’s the position in the range, why does it always return 1 ? Thanks for your help so far.
I tried several work-arounds, but nothing worked. I would hate to have to copy the row, then transpose it to a column and try it that way. It really impacts the functionality of the spreadsheet, seems like- - adding a new recipe won’t just be as easy as adding it to the right end of the array, anymore.
Your thoughts?.
You are asked twice
- for an example file
- for the precise command you used
As this seems no option for you, I think you have to solve this on your own…
.
In situations like this I would usually
- test, if examples from the help wor for me
- if not, test again in safe mode
- if examples work try “manual” MATCH() until it works
In most cases I found my errors during this process. But as I usually do this kind of stuff now in Base my actual approach is quite different.
3 Likes
$L$6:$L$90 is NOT a row, its one column between rows 6 and 90.
attach your document!
If I should guess:
=INDEX( $'Recipe Log'.$A$6:$ND$90 ;; MATCH( kriterion ; $'Recipe Log'.$L$6:$L$90 ; 0 ))
enter the Formula with ctrl shift enter
Looks like I’m on version 25.8.6.2 (X86_64)
For a drop–down menu, I look up one row from a table of recipes $‘Recipe Log’.$A$1:$ND$90, along row $L$6:$L$90.The drop-down menu works fine, and I select the desired recipe with it.
In a separate call, I need to know the column number so I can offset into the table to retrieve the recipe column of information. I tried all sorts of variations on
Sorry for all that, guys, and thanks for the help. It’s now fixed! (Examples below.)
I began correcting my problem by upgrading to the latest version of LibreOffice, version 26.2.4.2 (X86_64), on my Windows 11 laptop.
Next, I installed the drop-down menu in cell $‘Recipe Design’.B3 by using advice from https://www.libreofficehelp.com/data-validation-using-dropdown-list-libreoffice-calc/ I used row $‘Recipe Log’.$L$6:$ND$6 for the recipe names. (Sorry for misquoting this in my original post.)
Next, on my “Recipe Log” sheet in cell $A$4, I inserted =($‘Recipe Design’.B3) to echo the drop-down selection from sheet “Recipe Design”. Right below it, in $A$5, I entered text for the legend “column offset”, and in $A$6, the formula =MATCH($A$4,$L$6:$ND$6,0), which now works, returning the column offset into the recipe portion of the big array! (It didn’t, before.)
I can now use that offset to access all the details in the column data for the recipe, which I need elsewhere. Many thanks for your help.