Vlookup to find Exact Match

[AminShailja]

Hello I am trying to run vlookup on one workbook (dest.xlsm) row A1:A, from another workbook (Source.xlsm) row A1:A

Lets say, and ID( AA101) in Dest file has multiple findings in source file. Now I need only those records which has "Exceeded" in the adjacent row.
Please help me with this.

I have used vlookup through VBA Macros for this. but the result only fetches first row of every ID.

Anyone please can help me sort this out?

 

[Roger Govier]

Hi

Write your macro to Filter the data by ID, and Filter by Exceeded.
That will give you range of cells which match your criteria for you deal with as you wish.

 

[Special-K99]

in Dest!D1 put the ID you are looking for, e.g. AA101
in Dest!E1
=IFERROR(INDEX(Source!$B$2:$B$9,AGGREGATE(15,6,ROW(Source!$A$1:$A$9)/((Source!$A$2:$D$9=VLOOKUP(D$1,Dest!A$2:B$6,2,0))),ROWS(A$1:A1))-(1-1),1),"")
and copy down the column

 

[AminShailja]

@Special-K99, the code does not work effectively

 

[AminShailja]

Hi

Write your macro to Filter the data by ID, and Filter by Exceeded.
That will give you range of cells which match your criteria for you deal with as you wish.

Yes I will try this one. But what if there are two entries of the same ID with same status as EXCEEDED?

 

[Roger Govier]

You will get a list of all of the rows which have that ID and which contain the value Exceeded.
I thought that was what you were trying to achieve.
You don't say what you want to do with this information, but having determined through the VBA filter what that range is, you can then do whatever you wish with that information.

 

[Special-K99]

Ah yep, that doesnt work, difficult to test from photos
Try this

in Dest!D1 put the ID you are looking for, e.g. AA101
in Dest!E1
=IFERROR(INDEX(Source!$A$2:$A$9,AGGREGATE(15,6,ROW($D$2:$D$9)/((Source!$B$2:$B$9=VLOOKUP(D$1,Dest!A$2:B$6,2,0))),ROWS(A$1:A1))-(1-1),1),"")
and copy down the column

 

[AminShailja]

Ah yep, that doesnt work, difficult to test from photos
Try this

in Dest!D1 put the ID you are looking for, e.g. AA101
in Dest!E1
=IFERROR(INDEX(Source!$A$2:$A$9,AGGREGATE(15,6,ROW($D$2:$D$9)/((Source!$B$2:$B$9=VLOOKUP(D$1,Dest!A$2:B$6,2,0))),ROWS(A$1:A1))-(1-1),1),"")
and copy down the column

It returns 0 somehow for every row

 

[Special-K99]

Can you post a link to your file?
Upload it to a storage site

 

[AminShailja]

Can you post a link to your file?
Upload it to a storage site

I am affraid I can't upload any file out of company links.
Can you please take the data from the images I have sent?