Hello,
Please help anyone, i have a case like this:
i try with formula
=INDEX(D$1:D$9;MATCH(C$12;B$1:B$9;1);MATCH(B13;C$1:C$9;0))
but not give me the right value.
sory if my post to short, thanks for the help.
[Solved] match and index with 3 variables
[Solved] match and index with 3 variables
Last edited by coconutez on Sat Aug 19, 2017 9:08 pm, edited 1 time in total.
openoffice 4.0.1 on windows 7
Re: match and index with 3 variables
Another approach:
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: match and index with 3 variables
Here's an approach using INDEX(MATCH(...)) in an array formula.
Your approach doesn't give the expected results because there are multiple instances of the letter you want to look for. The array formula uses an IF() to render the non-matching rows as never matching the letter, so only the proper rows can MATCH. Also, the MATCH type option is set to zero so that the match must be exact and MATCH does not assume that the search range is in any particular order.
Here's the formula in C13:
{=INDEX($D$1:$D$9;MATCH($B13;IF($B$1:$B$9=C$12;$C$1:$C$9);0))}
(array formula, finish with Ctrl+Shift+Enter)
Your approach doesn't give the expected results because there are multiple instances of the letter you want to look for. The array formula uses an IF() to render the non-matching rows as never matching the letter, so only the proper rows can MATCH. Also, the MATCH type option is set to zero so that the match must be exact and MATCH does not assume that the search range is in any particular order.
Here's the formula in C13:
{=INDEX($D$1:$D$9;MATCH($B13;IF($B$1:$B$9=C$12;$C$1:$C$9);0))}
(array formula, finish with Ctrl+Shift+Enter)
- Attachments
-
- 123_acknak.ods
- (9.33 KiB) Downloaded 110 times
AOO4/LO5 • Linux • Fedora 23
Re: match and index with 3 variables
thank you very much sir for your help its slove the problem..
are there any other formulas without using helper tables?Zizi64 wrote:Another approach:
openoffice 4.0.1 on windows 7
Re: match and index with 3 variables
Thank you so much for the help sir
acknak wrote:Here's an approach using INDEX(MATCH(...)) in an array formula.
Your approach doesn't give the expected results because there are multiple instances of the letter you want to look for. The array formula uses an IF() to render the non-matching rows as never matching the letter, so only the proper rows can MATCH. Also, the MATCH type option is set to zero so that the match must be exact and MATCH does not assume that the search range is in any particular order.
Here's the formula in C13:
{=INDEX($D$1:$D$9;MATCH($B13;IF($B$1:$B$9=C$12;$C$1:$C$9);0))}
(array formula, finish with Ctrl+Shift+Enter)
openoffice 4.0.1 on windows 7
Re: [Solved] match and index with 3 variables
Without helper and without array formula :
Code: Select all
=VLOOKUP($B13;OFFSET($C$1:$D$3;MATCH(C$12;$B$1:$B$9;0)-1;0);2;0)
LibreOffice 6.4.5 on Windows 10