Problems with Index Match Function

Hello Everyone.

I am new here so I would first like to say hello to everyone. I also hope I can get my formula fixed below.

I need to create a formula that searches for a specific text in another worksheet and if it matches that specific text, it returns all of the information on that row. After doing some research, I came across the index match function, so I started using it and I keep getting a #N/A error and I can't figure out for the life of me what the problem is. Any help will be greatly appreciated. Thanks.



=INDEX('Online Renewal'!A:W,MATCH("BUS",'Online Renewal'!A:W,0))



*I am trying to search for BUS as a text on another worklist and return all of the information on that same row if it is a match.

**The column that I have it searching for BUS are also formulas. Would that cause a problem? Thanks.

Match can only search in a 1 dimensional array (either 1 row or 1 column).
So you can't do MATCH("BUS",'Online Renewal'!A:W,0)

Which column contains "BUS" on the Online Renewal page ?

Hi Jonmo1,

It is column A. So should it be =INDEX('Online Renewal'!A:W,MATCH("BUS",'Online Renewal'!A:A,0))? Thanks!

You also need to specify which column to RETURN a value from, based on where it is MATCHED in column A.

So say you want to return a value from Column B, based on the match in Column A
=INDEX('Online Renewal'!B:B,MATCH("BUS",'Online Renewal'!$A:$A,0))

Since you mention you want to drag this across to get all the data based on the row match, you need to lock the reference to column A
That's what the $$'s are for.

You might be better served even further by putting the match into it's own cell, say Z2 for example..
Z2: =MATCH("BUS",'Online Renewal'!A:A,0)
Then in B2 and filled right to X2 put
=INDEX('Online Renewal'!B:B,$Z2)