Alternative to Index Match

[benwork]

Hi all

Im using index match for text based values

=INDEX('sheet1'!$J$2:$J$30000,MATCH($B4, IF('sheet1'!$G$2:$G$30000=C4,'sheet1'!$H$2:$H$30000)))

The formula works great .. most of the time, although I have noticed sometimes it brings back ... random data. I dont understand why this is happening. I have checked the data to see if it is duplicated or soemthing like that. But havnt had any luck.

I would try a sumproduct except for the fact the cells are text based.

Any ideas ??

Cheers

Ben

 

[Andrew Poulsom]

Maybe you need an exact match:

=INDEX(Sheet1!$J$2:$J$30000,MATCH($B4, IF(Sheet1!$G$2:$G$30000=C4,Sheet1!$H$2:$H$30000),FALSE))

 

[benwork]

Thanks for the reply, tried that, still the same "random" results

 

[jbeaucaire]

I don't see the point of the IF() inclusion. Are you trying to do a Two-column Match? Maybe:

=INDEX('sheet1'!$J$2:$J$30000,MATCH($B4 & "-" & $C4, INDEX('sheet1'!$G$2:$G$30000 & "-" & 'sheet1'!$H$2:$H$30000, 0), 0)

If that formula is too cumbersome on your sheet, then consider adding another column to your sheet1 to concatenate these two values into a single matchable string. If you put this formula on sheet1 in AA2 and copied down:

=$G2 & "-" & $H2

Then the formula above simplifies down to:

=INDEX('sheet1'!$J:$J, MATCH($B4 & "-" & $C4, 'sheet1'!$AA:$AA , 0)

 

[benwork]

Yes, Im trying to do a 2 column match.

It normally works quite well, although for some reason I cant get it working.

I tried doing the forumla that you suggest but couldnt get it working

Is this correct ?


=INDEX(Sheet1!$J$2:$J$30000,MATCH($B4&"-"&$C4,INDEX(Sheet1!$G$2:$G$30000&"-"&Sheet1!$H$2:$H$30000,0),0))

 

[jbeaucaire]

The formula is right, but I just tried that and it is WAY too cumbersome if you're going to be putting many of these on your Sheet2. Please use the second suggestion to create a helper key column.

 

[benwork]

Nice one, got it working


Cheers

Ben