Match function

[rg3john2]

I am using the match function to look up a list of names in a tab on my spreadsheet. Next I want to return the project number in the same row, but 15 columns to the left in another tab in my workbook. The relative reference part is not working. If I ask the a number is put in the cell it works just fine, but I want to use the value to the far left of the same row.

=IF(ISNUMBER(MATCH("*"&C4&"*",MPL!Q1:Q32,0)),RC[-15],"")

Thank you,

Robin

[tigeravatar]

Robin,

Welcome to the forum!
Attached is an example workbook based on the criteria you described.
In cell D4 is uses this formula:

Please Login or Register  to view this content.

Does that work for you?

[Pete_UK]

Use INDEX with MATCH, like this:

=IF(ISNA(MATCH("*"&C4&"*",MPL!Q1:Q32,0)),"",INDEX(MPL!A1:A32,MATCH("*"&C4&"*",MPL!Q1:Q32,0)))

or:

=IF(COUNTIF(MPL!Q1:Q32,"*"&C4&"*")=0,"",INDEX(MPL!A1:A32,MATCH("*"&C4&"*",MPL!Q1:Q32,0)))

or, if you are using XL2007 or later:

=IFERROR(INDEX(MPL!A1:A32,MATCH("*"&C4&"*",MPL!Q1:Q32,0)),"")

Hope this helps.

Pete