1

LibreOffice Calc 5.1.6.2

I want to use VLOOKUP function with criterion being a reference to a cell again containing a reference. But it doesn't work, it returns #N/A. Interestingly, when I replace the reference with text value, it works except for the first row.

I'm attaching a mock file demonstrating this problem (sheet "normalized", column D.)

For future when the file is not available, textual version of the file below.

Description of the file:

  • 3 sheets: working, stats, normalized
  • the working sheet contains the raw data
  • the stats sheet shows sum counts for ind_codes from the working sheet
  • the normalized sheet shows again the ind_codes referenced from the working sheet and the corresponding sum count from the stats sheet

working sheet:

ind_code    count
111a    500
112a    400
113a    400
114a    400
111a    200
112a    300
113a    300
114a    300
111a    200
112a    300
113a    200
114a    300
111a    100
112a    100
113a    100
114a    100
HA  400
HB  100
HC  400
HD  100
HA  300
HB  200
HC  300
HD  200
HA  100
HB  100
HC  100
HD  100

stats sheet: (first 3 rows)

ind_code    sum_count
=working.A2     =SUMIF(working.A2:B1000,A2,working.B2:B1000)
=working.A3     =SUMIF(working.A3:B1001,A3,working.B3:B1001)
=working.A4     =SUMIF(working.A4:B1002,A4,working.B4:B1002)

normalized sheet: (first 3 rows)

ind_code    count       sum_count
=working.A2 =working.B2  =VLOOKUP(A2,stats.$A$1:$B$1000,2,0)
=working.A3 =working.B3  =VLOOKUP(A3,stats.$A$1:$B$1000,2,0)
=working.A4 =working.B4  =VLOOKUP(A4,stats.$A$1:$B$1000,2,0)
Improve this question
3
  • A cell can't be equal to a range.
    – fixer1234
    Commented Apr 12, 2018 at 17:02
  • @fixer1234: It does work even that way, but you're right, it's not nice, I have simplified the original post accordingly.
    – galapah
    Commented Apr 13, 2018 at 16:39
  • You won't get an error, but the comparison is made to just the first cell in the range. So you won't reliably get the right result.
    – fixer1234
    Commented Apr 13, 2018 at 16:48

2 Answers 2

Reset to default
0

The formula for cell A2 on the stats sheet is =working.A2:A1000. This does not look right to me. Why did you enter it that way?

It should simply say =working.A2, and then drag to fill down to row 1000. That fixes the problem.

Also, I can confirm that the spreadsheet gives an error in LO 5.1.6.2, but it does not in LO 6.0.3.2 or Apache OpenOffice 4.1.3.

Improve this answer
3
  • The range instead of single reference: I initially hoped to find a way how to mirror a whole range of cells from one sheet in another, just by entering a formula in one cell (like "show the array). And later on, when I entered the formula in every cell, it still worked this way, so I kept it.
    – galapah
    Commented Apr 13, 2018 at 16:30
  • It does work even that way, but you're right, it's not nice, I have simplified the original post accordingly.
    – galapah
    Commented Apr 13, 2018 at 16:39
  • I consider your answer a solution, i.e. your remark that it works in a higher version of LO, which I confirm. It was apparently a bug in the old (current stable) version.
    – galapah
    Commented Apr 13, 2018 at 18:27
-1

I solved it by opening the file in MS Excel 2017, it worked right away!

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .