Help on Vlookup vs VBA find

[TheEMVB]

Good afternoon Guys,

I'm really bad at vba. And I'm trying to understand the best way to do this procedure. Maybe you can help me.

I have a Workflow and every line has a LegId. This id is Case Sensitive (since there are equals IDs and the case sensitive is the only difference.
This LegId is on Collum C on the main sheet "TripReport".

what i have to look for: is the LEGID&cell "Z1"&cell "Z2" on "TripReport" ( ex: a0l0X000010BUchHandlingDeparture )
and search it on sheet "Services", collum C and retrieve the cell value on the collum N and copy to sheet "setup" cell "e45"

I try using formulas vlookup and index but doesn't work on case sensitive. I was hoping someone could help me.

Thanks

 

[DanteAmor]

But in column C of the "services" sheet you have something like this: "a0l0X000010BUchHandlingDeparture", then you need some like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]=VLOOKUP(C2&$Z$1&$Z$2,services!C:N,12,0)[/TD]
[/TR]
</tbody>[/TABLE]


Test and tell me

 

[TheEMVB]

But in column C of the "services" sheet you have something like this: "a0l0X000010BUchHandlingDeparture", then you need some like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]=VLOOKUP(C2&$Z$1&$Z$2,services!C:N,12,0)[/TD]
[/TR]
</tbody>[/TABLE]


Test and tell me

Hey... Thanks for the reply. But vlookup is not case sensitive. Doesn't work since i have 2 LEGIDs like this : 1- "a0l0X000010BUchHandlingDeparture" 2-"a0l0X000010BUcHHandlingDeparture"

THey could be the same but different on the Case

 

[DanteAmor]

Test:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]=INDEX(services!N1:N40,SUMPRODUCT((EXACT(services!C2:C40,C2&$Z$1&$Z$2))*ROW(services!C2:C40)))[/TD]
[/TR]
</tbody>[/TABLE]

Note: The range of N1 must start in row 1