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?
 
ProfessorJimJamCommented:
Hi Jagwarman,

please find attached.

simply run the macro   called "macro1"  and then it will do the job.
EE.xlsb
0
 
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
 
ProfessorJimJamCommented:
see the attached file the solution.
Book1.xlsx
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ProfessorJimJamCommented:
use Control Shift Enter for the formula i provided.
0
 
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
 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
press CONTROL +  SHIFT + ENTER   , Not just Enter
0
 
JagwarmanAuthor Commented:
how can I put that into a macro VBA code
0
 
ProfessorJimJamCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.