Using Index Match Function but not getting required result

0
votes

I have wrote a formula that is Index and match function and matching the sheet2 cell E9 and F9 with sheet1 Col"A" and Col"B".

If same values are exist then Index will copy the same row data of matching criteria then paste that data into Sheet2 as i did in below attached sheet.

Can someone please help with that problem, Result is coming accurate with single criteria but not with 2 criteria's. Your help will be appreciated.

=IFERROR(INDEX(Sheet1!$A$1:$CH$8,match($E$8,Sheet1!$A:$A,0),match($F$9,Sheet1!$B:$B,0),MATCH(E10,Sheet1!$3:$3,0)))

https://docs.google.com/spreadsheets/d/1iR_OTVGZhShafiDlIGUfWiRq8PWVR4r-49O7XDCqaFE/edit#gid=1525174863

2
excelexcel-formula
Test that match that is not working separately. Then once it works put it back in. - Solar Mike
Right, is there any way where they can match separately. - user14807564
Let me see if I can put this simply: put the match() into a separate cell to test it, once it works then test it back in the index() function. - Solar Mike
INDEX([Range], [Row number], [Column number]) returns a single cell's value. IFERROR(INDEX([Range], [Row number], [Column number]), [Column number]) returns a column number if INDEX returns an error. That's how you get the "5". Obviously, you mean to get another INDEX if the first one fails. Your formula doesn't say that. - Variatus
I added some formulas on your sheet. Take a look and see what you think. Yes, it is possible to do without the concatenation. You can do an array formula in Excel. I am not 100% sure if google sheets has the same functionality, but even if it does there are some performance issues with the array formula approach, especially over large ranges. - Chris Strickland

2 Answers

0
votes

index doesn't work this way. I'm not even sure how it's working at all. It takes 3 arguments, the range, a row and a column; you've given it four. What I would suggest is concatenating columns A&B into Column C on Sheet1, then you can do the match on the concatenation of $E$8 and $F$9. That would give you the row, so that will be the second argument to index. The column will be the third. That's your Sheet1!3:3 match, correct, but it's in the wrong place. Does this make sense

Take a look at your sheet. I have added the formula for the concatenation on Sheet1, and this formula in Column I of Sheet2:

=IFERROR(
    INDEX(
        Sheet1!$A$1:$CH$8,
        match($E$8 & " " & $F$9, Sheet1!$c:$c,0),
        MATCH(E10,Sheet1!$3:$3,0)
    )
)
-1
votes

You need to concatenate both values you are looking up for and the values you are searching.

Try this, which may need to be entered as an array formula i.e. with CTRL+SHIFT+ENTER.

=IFERROR(INDEX(Sheet1!$A$1:$CH$8,MATCH($E$8&$F$9,Sheet1!$A:$A&Sheet1!$B:$B,0),MATCH(E10,Sheet1!$3:$3,0)), "")