# Performing Case-sensitive Lookup, please explain how this code works

1. ## Performing Case-sensitive Lookup, please explain how this code works

=INDEX(C33:C37,MATCH(TRUE,EXACT(J29,B33:B37),0)) <--- How did (TRUE,EXACT(J29,B33:B37) became 3 which then became =INDEX(\$C\$33:\$C\$37,3)

Also,how did the dollar sign got there?

C33:C37
\$0.115
\$0.185
\$0.245
\$0.275
\$0.435

J29 = West

B33:37
NW
WEST
West
MidWest
East

2. ## Re: Performing Case-sensitive Lookup, please explain how this code works

That's actually an Array formula and you should see curly brackets around it.

{=INDEX(C33:C37,MATCH(TRUE,EXACT(J29,B33:B37),0))}

So the EXACT(J29,B33:B37) is creating an array: FALSE, FALSE, TRUE, FALSE, FALSE

And then MATCH(TRUE,EXACT(J29,B33:B37),0), returns 3 because the third entry is TRUE.

That gives you: INDEX(C33:C37,3) which returns the third value in the array C33:C37 which is \$0.245.

The dollar is presumably because the cell is formatted as currency.

Regards, TMS

3. ## Re: Performing Case-sensitive Lookup, please explain how this code works

Hi Dwexdwex,
In order for this formula to work it must be entered as an array formula (by pressing CTRL + SHIFT + ENTER). As an array formula MATCH(TRUE,EXACT(J29,B33:B37) looks in every cell between b33 and b37, one at a time. If it finds an exact copy of cell J29 within that range it returns true and continues to the next item. the result is a series of results that looks like this {FALSE, FALSE, TRUE, FALSE, FALSE}. Match then looks in this series of results, NOT cells b33:b37, and determines that the third entry in the series matches what it is looking for, namely TRUE and thus the result of 3. Index then looks up the third entry in the range c33:c37 which is 0.245.

The Dollar signs come from the formatting which has been chosen. You can change it by going to Home>Numbering>Number Formatting drop down box.

4. ## Re: Performing Case-sensitive Lookup, please explain how this code works

Hi Dwexdwex,

the match(true...) returns the position of the first TRUE within that array, for example the position of the first character in Cell B33:B37 that is an exact match of one in Cell J29; the INDEX function returns the code in Column C that is stored in corresponding position

Reference: F1 Get the most out of excel functions & formulas.

5. ## Re: Performing Case-sensitive Lookup, please explain how this code works

Thanks for the rep.

New quick method:

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1