asked on

Match formula

Hi,

I need need formula in cell AR2 that looks at the entry in AQ2, and looks that up against Col P in another workbook named "MappingTable.xls", and when a match is found returns the entry in Col G of "Mapping Table.xls", if no match is found, it should return "No PCM found"

Many thanks

Guy Hengel [angelIII / a3]

if it was in the same workbook, this is the approach, in cel

=IF(ISNA(MATCH(AQ2;P:P;0));"No PCM found";OFFSET(G$1;MATCH(AQ2;P:P;0)-1;0))

for the data being in the other workbook:

=IF(ISNA(MATCH(AQ2;'[MappingTable.xls]Sheet1'!P:P;0));"No PCM found";OFFSET('[MappingTable.xls]Sheet1'!G$1;MATCH(AQ2;'[MappingTable.xls]Sheet1'!P:P;0)-1;0))

Seamus2626

ASKER

Thanks guty, im a gettig a syntax error on that one

Naresh Patel

try this

=IF(INDEX('[Mapping Table.xls]Sheet1'!$G:$G,MATCH(AQ2,'[Mapping Table.xls]Sheet1'!$P:$P,0))=0,"No PCM Found",INDEX('[Mapping Table.xls]Sheet1'!$G:$G,MATCH(AQ2,'[Mapping Table.xls]Sheet1'!$P:$P,0)))

SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

itjockey: Your formula returns all #NA
NBVC; Your formula returns all "No PCM FOUND"

I am currently ussng a vlookup, where i have shifted the columns about, so to work a vlookup, but would prefer match formula - please note below is in VBA notation

=IFERROR(IF(VLOOKUP(A2,'[MappingTable.xlsm]GBM Mapping Table v4'!$B:$F,5,0)=0,""No Sub sector found"",VLOOKUP(A2,'[MappingTable.xlsm]GBM Mapping Table v4'!$B:$F,5,0)),""No Sub Sector found"")"

Thanks

NBVC

oops... I might have switched the references... try:

=IFERROR(INDEX('[MappingTable.xls]Sheet1'!G:G,MATCH(AQ2,'[MappingTable.xls]Sheet1'!P:P,0)),"No PCM FOUND")

Seamus2626

ASKER

I actually switched them over, but its still returning all "No PCM FOUND" !!

ASKER CERTIFIED SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

All sorted guys, i used a vlookup in the end, thanks!