Problem with INDEX/MATCH It work for first 3 row only

[joeeee]

Hi

I have a problem with INDEX/MATCH formula. I have many scenarios writen in second sheet named 'source'. Single scenario has 3 rows with same number of scenario. Column A and B are source for this index/match formula



this formula i have in C2

IF($b2="";"";INDEX(Source!D:D;MATCH($b2;Source!C:C;0);MATCH($a2;Source!B:B;0)))

This formula is also in whole C column.





[TABLE="width: 500"]
<tbody>[TR]
[TD]Scenario number[/TD]
[TD]CROP[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cabbage[/TD]
[TD]Redma F1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lettuce[/TD]
[TD]Decision F1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Onion[/TD]
[TD]Tamara F1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Peppers[/TD]
[TD][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ref]#ref [/URL] ![/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tomato[/TD]
[TD][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ref]#ref [/URL] ![/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Potato[/TD]
[TD][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ref]#ref [/URL] ![/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Lettuce[/TD]
[TD][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ref]#ref [/URL] ![/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tomato[/TD]
[TD][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ref]#ref [/URL] ![/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Onion[/TD]
[TD][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ref]#ref [/URL] ![/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cabbage[/TD]
[TD]Redma F1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lettuce[/TD]
[TD]Deciosion F1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Onion[/TD]
[TD]Tamara F1[/TD]
[/TR]
</tbody>[/TABLE]


I have another sheet with name SOURCE where i have data related to this INDEX MATCH formula. It works fine, but only for the first scenario, which is under number 1. If i put scenario number 1 in any other cell (like in cell c11) formula work great.

But when i wanna other data from othe scenarios like scenario 2 or 3 i get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ref]#ref [/URL] ! If i put scenario number 2 or 3 in cell c2 i get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ref]#ref [/URL] !


Where or what is problem here ?

 

[mole999]

any spaces around source v lookup, as that will effect values

 

[barry houdini]

=IF($b2="";"";INDEX(Source!D:D;MATCH($b2;Source!C:C;0);MATCH($a2;Source!B:B;0)))

Hello joeeee, welcome to MrExcel

I doubt your formula is correctly written for what you want to do. In the above the first MATCH defines the row number and the second MATCH (in red) defines the column number......but because your INDEX range is a single column only (Source!D:D) you'll get a #REF! error if that MATCH returns a value > 1.

If you want to return the value from Source!D:D in the row where the other two conditions match then you need this version:

=IF($b2="";"";INDEX(Source!D:D;MATCH(1,INDEX(($b2=Source!C:C)*($a2=Source!B:B);0);0)))

......although it's also advisable to use shorter ranges rather than whole columns​

<strike></strike>

 

[joeeee]

Thank you for your help. Now it works great