LibreOffice Calc 184.108.40.206
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
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)