Ask Your Question

Bgs's profile - activity

2016-03-02 12:53:00 +0100 received badge  Famous Question (source)
2014-10-31 22:36:02 +0100 received badge  Famous Question (source)
2014-08-19 16:57:38 +0100 received badge  Notable Question (source)
2014-08-19 16:44:55 +0100 commented answer value of merged cell next to a non-merged one

In the above simplified case it's a static string to look up. In my example B42.

I didn't want to include the final (whole) formula as it's long and the real information regarding this post gets lost in the lot. :)

The real information is:

  • Take the row number of the B cell.
  • Create an array from that row in column A to the top.
  • Do a match -1 with an empty string on it.
  • From the match output you get the row number of the merged cells first cell which actually contains the data.
2014-08-19 16:39:33 +0100 commented answer value of merged cell next to a non-merged one

It doesn't really matter what the format or ordering is. I have multiple tables with this structure. I picked the date for easier understanding. In another case for example I have mac addresses grouped by switch port or port-channel. :) In practical terms those are randomly ordered free form strings.

2014-08-19 15:56:56 +0100 received badge  Editor (source)
2014-08-19 15:40:36 +0100 received badge  Popular Question (source)
2014-08-19 15:39:50 +0100 commented answer value of merged cell next to a non-merged one

It actually works with match -1:

=MATCH("", INDIRECT("sheet1.A1:A"&RIGHT(sheet2.a1,LEN(sheet2.a1)-1)), -1)

The above gives the row number. I intentionally left out how to get the value from that as it doesn't add to the real problem solving.

2014-08-19 15:29:39 +0100 commented answer value of merged cell next to a non-merged one

Sheet1 of my example is generated and I cannot change its formatting. All the fancy work has to be done from/on sheet2.

2014-08-19 15:28:12 +0100 commented answer value of merged cell next to a non-merged one

The hidden cells are indeed empty. The logical algorithm this way is simple: step left -> while cell empty, step up -> Get cell value.

But I do not see any obvious way either to achieve the same with functions. I guess it can be done by scripting it, but I've never done LO scripting.

Theoretically something like this should work, but I wanted to see if there is a more straightforward way: From the coordinate B42 create an array of A1:A42 -> Do some lookup from bottom up for non-empty cell and return value.

2014-08-19 14:30:59 +0100 asked a question value of merged cell next to a non-merged one

I have a table where column B contains individual data and column A has several merged cells that informally group the B cells. Let's say B contains transaction IDs and A contains merged cells where each merged cell contains a date string for the time ordered transactions.

In another sheet in whatever way I get a reference to a B cell (for example the output of a function is sheet1.B42). I need to get the A value for that B value. For example B42 happens to be next to the A38:A52 merged cell. That is I need the value of A38.

To sum up: sheet2.A1 -> function with a value of "sheet1.B42" (result of a lookup) sheet1.B42 is next to the merged cell A38:A52 sheet2.B1 should get the value of sheet1.A38

How do I make that sheet2.A1 -> sheet1.A38 connection?

2014-08-19 14:19:38 +0100 received badge  Supporter (source)
2014-08-19 14:19:36 +0100 received badge  Scholar (source)
2014-07-28 12:30:16 +0100 received badge  Notable Question (source)
2014-07-14 09:32:26 +0100 received badge  Popular Question (source)
2014-07-12 11:09:29 +0100 received badge  Student (source)
2014-07-11 21:15:53 +0100 asked a question How can I use nested VLOOKUPs?

I'm trying to give the lookup array for a VLOOKUP as a named array coming from another VLOOKUP.


1) This works: VLOOKUP(C2, VendorExams, 3, 0)

I get as output CAT_exams which is the name of an array.

2) This works too: VLOOKUP(C2&D2, CAT_names, 4, 0)

I get as output the desired cell value from the CAT_names array.

3) Nesting them with (or without for that matter) INDIRECT, doesn't work: INDIRECT(VLOOKUP(C2&D2, VLOOKUP(C2, VendorExams, 3, 0), 4, 0))

I get an 504 error which indicates to me that the nested VLOOKUPs output is not understood.

Is this not-working-as-intended, a bug or am I doing something in a wrong way?

Using 4.2.3 if it has any relevance.