SOLVED

# Two-way Index Match help

Brass Contributor

# 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

8 Replies
best response confirmed by reevesgetsaround (Brass Contributor)
Solution

# Re: Two-way Index Match help

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.

# Re: Two-way Index Match help

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.

# Re: Two-way Index Match help

Thank you Hans, that worked very well.

# Re: Two-way Index Match help

This is great! Will be sure to ise this in future - Thank you.

# Re: Two-way Index Match help

how do you unfreeze columns?

# Re: Two-way Index Match help

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 thanks

# Re: Two-way Index Match help

Like 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", "")

1 best response

Accepted Solutions
best response confirmed by reevesgetsaround (Brass Contributor)
Solution

# Re: Two-way Index Match help

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.