[Solved] match and index with 3 variables

Discuss the spreadsheet application
Post Reply
coconutez
Posts: 3
Joined: Sat Aug 19, 2017 9:57 am

[Solved] match and index with 3 variables

Post by coconutez »

Hello,
Please help anyone, i have a case like this:
123.ods
(9.61 KiB) Downloaded 86 times
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.
Last edited by coconutez on Sat Aug 19, 2017 9:08 pm, edited 1 time in total.
openoffice 4.0.1 on windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: match and index with 3 variables

Post by Zizi64 »

Another approach:
123_Vlookup.ods
(11.42 KiB) Downloaded 86 times
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.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: match and index with 3 variables

Post by acknak »

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)
Attachments
123_acknak.ods
(9.33 KiB) Downloaded 110 times
AOO4/LO5 • Linux • Fedora 23
coconutez
Posts: 3
Joined: Sat Aug 19, 2017 9:57 am

Re: match and index with 3 variables

Post by coconutez »

thank you very much sir for your help its slove the problem..
Zizi64 wrote:Another approach:
123_Vlookup.ods
are there any other formulas without using helper tables?
openoffice 4.0.1 on windows 7
coconutez
Posts: 3
Joined: Sat Aug 19, 2017 9:57 am

Re: match and index with 3 variables

Post by coconutez »

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
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: [Solved] match and index with 3 variables

Post by gerard24 »

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
Post Reply