Using INDEX and MATCH functions to find repetitive matches

[REUBEN]

Hi,

This topic was discussed on another thread but the problem hasn't been resolved.

I copy information from one report to the other. The first report (A), I use as my case work tracker and have a lot of information on. But only copy few details of each case to the other workbook (B) i.e. a shared workbook used by the rest of the team on a share drive. Basically when I copy the case data to from, A to B a lot of details get copied on to B (including the client name column). I am able to auto-populate in A is the serial number corresponding to that client's name in B. And this is done by the following formula.

=INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202;MATCH(D75;'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$F$3:$F$202;0))

However, if the name repeats in the column F, the formula gave the first instance where the name appears as the result. Which is not the correct serial number, I need it to find the serial number for the last/latest entry of the respective names from the bottom as against the first from the top which the formula is providing now.

Any suggestions?

 

[Aladin Akyurek]

@REUBEN

Try to adapt the following set up to your destination workbook:
http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

 

[REUBEN]

Thank you. will explore that option.

 

[REUBEN]

Hi again,

I'm sorry that was a bit too technical for me to understand. My excel skills are quite basic I must say.

Let me try and explain what I'm trying to do. I'm trying to get the serial number in the left most column on workbook B (on the shared drive for the team) that corresponds to the name in each row on column D of workbook A (on my system).

This formula is working very well - =IF(ISBLANK(D4);""; LOOKUP(2;1/('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$F$3:$F$202=$D4);'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202))

except when the name does not exist in the sheet the formula is looking in - '[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '. The name would then either be in sheet '[CPH RELO Reporting 2014.xlsx]IMMIG -Immig Company ' or in '[CPH RELO Reporting 2014.xlsx]IMMIG - Direct '.

All these three sheets are in the same workbook B.

So what I now need to do is if the name is not found in that sheet, then simply look for it in the other two sheets.

I hope this helps.

 

[Aladin Akyurek]

Hi again,

I'm sorry that was a bit too technical for me to understand. My excel skills are quite basic I must say.

Let me try and explain what I'm trying to do. I'm trying to get the serial number in the left most column on workbook B (on the shared drive for the team) that corresponds to the name in each row on column D of workbook A (on my system).

This formula is working very well - =IF(ISBLANK(D4);""; LOOKUP(2;1/('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$F$3:$F$202=$D4);'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202))

except when the name does not exist in the sheet the formula is looking in - '[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '. The name would then either be in sheet '[CPH RELO Reporting 2014.xlsx]IMMIG -Immig Company ' or in '[CPH RELO Reporting 2014.xlsx]IMMIG - Direct '.

All these three sheets are in the same workbook B.

So what I now need to do is if the name is not found in that sheet, then simply look for it in the other two sheets.

I hope this helps.

You seem to have a different task than I presumed... Try:

=IF($D4="";"";
  IFERROR(INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202;
   MATCH($D4,'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$F$3:$F$202;0));
  IFERROR(INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG -Immig Company '!$A$3:$A$202;
   MATCH($D4,'[CPH RELO Reporting 2014.xlsx]IMMIG -Immig Company '!$F$3:$F$202;0));
  IFERROR(INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG - Direct '!$A$3:$A$202;
   MATCH($D4,'[CPH RELO Reporting 2014.xlsx]IMMIG - Direct '!$F$3:$F$202;0));
   "Not Found")))

Try to remove the trailing spaces in the sheet names in your source workbook and adjust the formula above accordingly.

 

[REUBEN]

Like somebody had said in a post earlier... You're the king! :D

Thank you that formula worked just right, I tweaked it to fit the variations in the other sheets.

Here's the final formula that did it.

=IF($D4="";"";IFERROR(INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202;MATCH($D4;'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$F$3:$F$202;0));IFERROR(INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG - Immig company'!$A$3:$A$202;MATCH($D4;'[CPH RELO Reporting 2014.xlsx]IMMIG - Immig company'!$G$3:$G$202;0));IFERROR(INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG - Direct'!$A$3:$A$202;MATCH($D4;'[CPH RELO Reporting 2014.xlsx]IMMIG - Direct'!$F$3:$F$202;0));"Not Found"))))

I still do see a few "Not Found" messages, which could just be due to the way those names are entered on the workbook B. Will have that sorted out myself.

Thank you once again.

Reuben

 

[Aladin Akyurek]

Like somebody had said in a post earlier... You're the king! :D

Thank you that formula worked just right, I tweaked it to fit the variations in the other sheets.

Here's the final formula that did it.

=IF($D4="";"";IFERROR(INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202;MATCH($D4;'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$F$3:$F$202;0));IFERROR(INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG - Immig company'!$A$3:$A$202;MATCH($D4;'[CPH RELO Reporting 2014.xlsx]IMMIG - Immig company'!$G$3:$G$202;0));IFERROR(INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG - Direct'!$A$3:$A$202;MATCH($D4;'[CPH RELO Reporting 2014.xlsx]IMMIG - Direct'!$F$3:$F$202;0));"Not Found"))))

I still do see a few "Not Found" messages, which could just be due to the way those names are entered on the workbook B. Will have that sorted out myself.

Thank you once again.

Reuben

You are welcome.