Forum Discussion
Two-way Index Match help
Hello
I need some assistance in converting some horizontal data into table format, beginning at column H in the attachment.
It should be some variation on an Index Match I'd imagine but there's the added obstacle of checking against a 'Yes' column, and transposing it to the table. (Unfortunately I'm unable to alter this in the real-world data I'm using)
As always, all help is appreciated.
Many Thanks
Perhaps in H2
=INDEX($C$2:$C$26,MATCH(1,($A$2:$A$26=$G2)*($B$2:$B$26=H$1),0))&""
If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.
Fill down, then to the right or vice versa.
8 Replies
- how do you unfreeze columns?
Please ask that in a new discussion, instead of replying to a totally unrelated one.
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
- This is great! Will be sure to ise this in future - Thank you.
Perhaps in H2
=INDEX($C$2:$C$26,MATCH(1,($A$2:$A$26=$G2)*($B$2:$B$26=H$1),0))&""
If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.
Fill down, then to the right or vice versa.
- Hi Hans,
The formula you provided worked just fine in what I was doing so thank you for that. Though, just out of interest, is there anyway in which the formula can be modified so that it only picks up a specific text, e.g. 'Yes'? As I believe on this current iteration it will pick out any of the text from column C.
Just wondered.
Many thanksLike this:
=IF(INDEX($C$2:$C$26,MATCH(1,($A$2:$A$26=$G2)*($B$2:$B$26=H$1),0))="Yes", "Yes", "")
or
=IF(INDEX($C$2:$C$26,MATCH(1,($A$2:$A$26=$G2)*($B$2:$B$26=H$1),0))="Yes", "Available", "")
- Thank you Hans, that worked very well.
