Using INDEX and MATCH functions to find repetitive matches

R

REUBEN

Board Regular
Joined
Mar 7, 2014
Messages
113
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?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Maybe try:

=LOOKUP(2;1/('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$F$3:$F$202=$D$75);'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202)
 
Upvote 0
Hi Steve,

That correction does work fine. Thanks a ton for that! :)

But I'm now facing an unusual issue with copying that formula down that entire row. Plus it was not working for cells that were empty so I added a little prefix to it.

=IF(ISBLANK(D75);""; LOOKUP(2;1/('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$F$3:$F$202=$D$75);'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202))

However, copying the formula still has the problem that the 'Lookup vector' of ....$D$75 remains constant so the formula needs to be manually changed when I copy it to each cell. :(

Any suggestions?
 
Upvote 0
$D$75
$D75
D$75
D75

Play with the differances and try to understand, what they are doing.

If you understand it well, you will be able to produce a solution on your own.
 
Upvote 0
Thank you, Oeldere!

that worked just fine.

But now the fields where the name doesn't match any entry in the other sheet gives me an N/A Error. This is because those entries (names) are from another sheet.

So for such entries where I am getting the N/A, could I give it an option to check the other sheet's reference?

Thanks again.
 
Upvote 0
Code: 
However, copying the formula still has the problem that the 'Lookup vector' of ....$D$75 remains constant so the formula needs to be manually changed when I copy it to each cell.


Without seeing your data it is hard to tell (at least for me).

But is you know what the dollor sign accomplish,

and

you know how your data is set up


you will know which of the options ($B$75, $B75, B$75, B75) you will need, to get the formula working.
 
Upvote 0
Hi again.

Here's a screen shot of the workbook A:
25zmaag.jpg


And here's Workbook B.
2i293k3.jpg


so you see it could be possible that sometimes the names are in the other pages of workbook B.

And that's why in A you can see I am getting the N/A error message.

Hope this is helpful.

Thanks again.
 
Upvote 0
Hi again Steve,

I'm sorry but your suggested method returns an error if I use it in the formula:

Your suggestion- "=IF(ISBLANK(D75);"";IFERROR(formula;other formula))"


=IF(ISBLANK(D36);""; IFERROR(LOOKUP(2;1/('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$F$3:$F$202=$D36);'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202))

If you look at the workbook B on the image i posted above, it shows two other sheets that may have the data of names and I'd have to tweak the above formula to get the right serial number.

Hope this helps.
 
Upvote 0
In that case, why not all data on 1 sheet an use a helpcolumn to determine whith the differance (e.g. tab-name your using now).
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Using Vlookup or Index/Match across 2 workbooks
Replies
1
Views
187
DataKnight
D
P
  • Solved
VBA Match data, find mismatch data and copy data to validation sheet. Not working correctly.
2
Replies
16
Views
768
pawcoyote
P
T
  • Question
Help with formula
Replies
3
Views
233
awoohaw
awoohaw
J
  • Solved
Index and Match should be my best option?
Replies
5
Views
344
Fluff
Fluff
C
  • Solved
Index match function
Replies
3
Views
453
Alex Blakenburg
A

Forum statistics

Threads
1,214,887
Messages
6,122,095
Members
449,064
Latest member
Danger_SF

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top