Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# index match not giving correct result

Brass Contributor

# index match not giving correct result

I have tried various reiterations of formulas & either I get the incorrect result or a N/A.

Any help would be greatly appreciated.  I have attached the file.

5 Replies

# Re: index match not giving correct result

``=INDEX(\$D\$10:\$I\$19,MATCH(1,(\$B\$10:\$B\$19=\$B5)*(\$C\$10:\$C\$19=\$A5),0),MATCH(D\$4,\$D\$9:\$I\$9,0))``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

# Re: index match not giving correct result

thank you. What is vergleich? I am unfamiliar with that funcion

# Re: index match not giving correct result

VERGLEICH in german Excel is MATCH in english Excel. I've translated the whole formula in my first reply. When you open the attached file of my first reply the formula is automatically translated into your language.

# Re: index match not giving correct result

@OliverScheurich included the English version of the formula in the text of his reply; the screenshot shows the German version. VERGLEICH is the German name of the MATCH function.

# Re: index match not giving correct result

Based on the arrangement of your data and the extra argument added to your INDEX, I'm guessing you were trying to use the 'reference' arrangement of INDEX.  It's not commonly used but a solution would look like this (note the parenthesis enclosing the 3 ranges in the first argument):

``````=INDEX(
(\$D\$10:\$I\$11, \$D\$14:\$I\$15, \$D\$18:\$I\$19),
XMATCH(\$A5, \$C\$10:\$C\$11),
XMATCH(D\$4, \$D\$9:\$I\$9),
XMATCH(\$B5, {"ABC", "DEF", "GHI"})
)``````