Exact Vlookup

[mdmackillop]

Can Exact be combined with Vlookup (or other function) to provide a case sensitive Vlookup solution for text values a,A,b,B,c,C etc.

[Simon Lloyd]

This thread may be of use to you malcom http://forums.crmsuccess.com/sforce/board/print?board.id=practices&message.id=2034&page=1&format=all it has this formula =IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOKUP(C1,A1:B5,2,FALSE),"No exact match")which doesnt work exactly but there is a worked example of converting your search.

Regards,
Simon

[Shazam]

Can Exact be combined with Vlookup (or other function) to provide a case sensitive Vlookup solution for text values a,A,b,B,c,C etc.


Try...


=VLOOKUP("a",IF(EXACT(A1:A21,"a"),A1:B21),2,0)

Or

=INDEX(B2:B21,MATCH(TRUE,EXACT("a",A2:A21),0))

It's an-array both needs to be hold down:

Ctrl,Shift,Enter

Hope it helps!

[coliervile]

Thanks malcom for asking the question.

On the "Revised" worksheet in column "B", B3:B25 are shifts that employees work and they are case sensitive. In column "A", A29:A53 are the actual shifts and there corresponding hours. Note that some are 8 hour shifts and some are 9 hours, e.g. "b" 0530-1430 and "B" 0530-1330. The VLOOKUP doesn't work necessarily because it finds the first letter b and uses that time. I need a formula like in a INDEX-MATCH-EXACT in cells C3:C25 and D3:D25 to look into B3:B25 and if it matches exactly A29:A53 insert the the corresponding start and end time from B29:B53 and C29:C53 into cells C3:C25 and D3:D25.

I hope that it made sense :bug:

See attached file:

Regards
Charlie

[coliervile]

I should have added that in column B3:B25 change depending on the date entered in cell A1. When a date is changed in cell A1 the shift identifiers in cells B3:B25 change based off of "sheet1". Example "worksheet1" on 11/16/06 employee JP is working a "e" (0830-1730) and then on 11/17/06 employee JP is working a "B" (0530-1330). When cell A1 on "Revised" worksheet is changed from 11/16/06 to 11/17/06 cells B3:B25 will change. I'm not sure if that makes the INDEX-MATCH-EXACT more complicated or another type of formula is best suited for this?

Regards

Charlie

[Shazam]

See if this works.

Input formula in cell E2 copy across to cell F2 and filldown both cells.

=INDEX(B$30:B$53,MATCH(TRUE,EXACT($B3,$A$30:$A$53),0))

Formula is an-array must hold down:

Ctrl,Shift,Enter

[coliervile]

Shazam that exactly what I was looking for...no pun intended.

Thanks you Malcom and Shazam.

Best Regards
Charlie