Vlookup question

with VLookup I understand that it will return the first occurrence, i.e.

My VLookup is : =IFERROR(IF(VLOOKUP($A1,ADWRFAIL2!A:B,2,0)="R",K1,J1),K1)      

Column A contains ABC123456
Column B Contains R or S or T
Clolumn K contains a date

so, if there are 3 items with ABC123456 but each has a different letter in Col B .....

ABC123456      T      18/04/2015
ABC123456      S      19/04/2015
ABC123456      R      20/04/2015

Is it possible to make the Vlookup return the correct one. 20/04/2015
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
You could add a concatenation column to the data (and hide it if necessary).

Something like the attached.

Rgds
EE.xls
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
see the attached file the solution.
Book1.xlsx
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
use Control Shift Enter for the formula i provided.
0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Rgonzo1971Commented:
HI,

Maybe

=IFERROR(INDEX(K:K,MATCH($A1&B1,ADWRFAIL2!A:A&ADWRFAIL2!B:B,0)),K1)

not sure what you want to show if not found

Regards
0
JagwarmanAuthor Commented:
ProfessorJimJam when I open your file I can see it works but as soon as I click on the cell to read the code I get #N/A. Also I would need to code this into my VBA and when I tried that it also returned #N/A
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
you get N/A  becuase when you click on cell you cannot just simply enter .  you have to press CONTROL SHIFT ENTER

then it will not give you N/A
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
press CONTROL +  SHIFT + ENTER   , Not just Enter
0
JagwarmanAuthor Commented:
how can I put that into a macro VBA code
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
Do you have a VBA code already? or do you want me to write one for you?

if you want me to write the code for you to populate the formula into the cell via VBA, then i need the file which you are working on with its data.  at least a sample of it, if you cannot share your file, due to its confidentiality.
0
JagwarmanAuthor Commented:
ProfessorJimJam & Rgonzo1971 I have attached a sample file.

many thanks
VLookup.xlsm
0
Rob HensonFinance AnalystCommented:
Different approach:

=IF(COUNTIFS(adwrfail2!A:A,A2,adwrfail2!B:B,"R")=1,K2,J2)

This counts the number of entries where column A of 'adwrfail2' matches the value in A2 of sheet3 AND the corresponding value in column B of 'adwrfail2' is R. If it equals 1 then use K2 else use J2.

Thanks
Rob H
0
Rgonzo1971Commented:
HI,

to use rob's formula, pls try
Set myRange = Range(Range("M2"), Range("M" & Range("A" & Cells.Rows.Count).End(xlUp).Row))
myRange.Formula = "=IF(COUNTIFS(adwrfail2!A:A,A2,adwrfail2!B:B,""R"")=1,K2,J2)"

Open in new window

Regards
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
Hi Jagwarman,

please find attached.

simply run the macro   called "macro1"  and then it will do the job.
EE.xlsb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Slight amendment to my suggestion, if there is the chance that there could be more than one entries that match the ID in column A and have an R in column B, change to:

=IF(COUNTIFS(adwrfail2!A:A,A2,adwrfail2!B:B,"R")>0,K2,J2)

Thanks
Rob H
0
JagwarmanAuthor Commented:
I really don't like the way I have to allocate points when all 3 of you deserve 500 on this. Many thanks for your help with this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.