Hi James,
James schrieb am 22.06.2024 um 22:12:
I am having trouble with vlookup.
I put a test file at:
http://www.mediafire.com/file/puq9nuu982beicl/vlookup-test.ods/file
The spreadsheet is:
One $1 1%
Two $2 2%
Three $3 3%
current $2 2% #N/A
C4 contains =VLOOKUP(B4,B1:C3,2,0) which works.
D4 contains =VLOOKUP(C4,A1:C3,1,0) which displays #N/A but I want it to
display whatever is in column A of the matching row.
"Two" in this case.
VLOOKUP cannot access a column that is before the left-most column of
the lookup-range, and the left-most column of the lookup-range is always
the column, where the value is searched.
Whatenever I change B4, D4 should display the matching row of column A/
This can be done with a combination of MATCH with OFFSET or a
combination of MATCH with INDEX.
For the combination of MATCH with OFFSET you need to add a row of labels
before your data.
ColA colB ColC
Row1 labelA labelB labelC
Row2 One $1 1%
Row3 Two $2 2%
Row4 Three $3 3%
Row5 current $2 see below
formula in cell C5
=OFFSET(A1; MATCH(B5;B2:B4;0);0)
A1 is the reference cell. To its position OFFSET adds the row difference
and the column difference. MATCH returns 2. Thus the returned value is
from cell column A+0, row 1+2, that is from cell A3.
For the combination of MATCH with INDEX you need no label row.
ColA colB ColC
Row1 One $1 1%
Row2 Two $2 2%
Row3 Three $3 3%
Row4 current $2 see below
formula in cell C4
= INDEX(A1:C3;MATCH(B4;B1:B3;0);1)
Index uses the position row|col whereby the left-top cell in the
reference area A1:C3 has the position 1|1. Thus position 2|1 is the cell A2.
The upcoming version 24.8 will have a function XLOOKUP. With that
function the lookup range and the return range are separated.
=XLOOKUP(B4; B1:B3; A1:A3)
(My parameter separator is a semicolon. You need to adapt the formulas
to use your parameter separator.)
Kind regards,
Regina
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
Privacy Policy: http://www.documentfoundation.org/privacy
