Case Sensitive Reverse Vlookup Using Index Match in Google Sheets

0
231
Case Sensitive Reverse Vlookup

I believe Case Sensitive Reverse Vlookup Using Index Match is new to you. Reverse Vlookup in Google Sheets is Possible with Index / Match function combo. This is also called backward Vlookup in Google Sheets. But what about a Case Sensitive Reverse Vlookup in Google Sheets.

Vlookup is the favourite function of all spreadsheets aspirants. But it has few drawbacks. The most prominent one among them is reverse Vlookup or backward Vlookup.

A combination of Index and Match function can work similar to Vlookup and it’s better than Vlookup. Advanced users like to use the Index Match Combination as an alternative to Vlookup.

Our Best Vertical Lookup Tutorials:

1. Learn Vlookup and Hlookup

2. Case Sensitive Vlookup in Google Sheets

3. VLOOKUP with Multiple Criteria in Google Sheets

4. Dynamic Lookup: Vlookup to Find Nth Occurrence in Google Sheets

5. Index Match – Better Alternative to Vlookup and Hlookup

Here in this tutorial, you can learn two things. Case Sensitive Index Match and Case Sensitive Reverse Vlookup Using Index Match. So here we begin.

Case Sensitive Index Match [Case Sensitive Vlookup Alternative]

Case sensitive Vlookup in Google Doc Spreadsheet is possible only with a Helper Column or Virtual Helper Column. You can check the above tutorial # 2 to learn this. But with Index Match, without a helper column, you can do a case sensitive Vlookup. Here is the sample data and formula.

Index Match Case Sensitive Vertical Lookup Formula

Formula:

=index(A2:G5,match(1,ArrayFormula(FIND(C7,A2:A5)),0),5)

Explanation:

Before explaining the above Index Match Case Sensitive formula, see the same Case In-sensitive formula below.

=index(A2:G5,match(C7,A2:A5,0),5)

Now let me compare this formula with Index Function Syntax.

INDEX(reference, [row], [column])

Here the only confusing part is the “row”. Since we don’t have the row number and only know the search key in C7 as “IL102b”, I’ve used Match function to return the row number using the search key.

Hope you could understand this comparison. If you have any doubt, switch to our tutorial # 5 above. Now to the explanation part of our Case Sensitive Index Match formula.

See the formula again and the colouring pattern.

=index(A2:G5,match(1,ArrayFormula(FIND(C7,A2:A5)),0),5)

Again take a look at the blue coloured “row” part. Here additionally I’ve used FIND function inside Match. The reason, Find is a case sensitive function in Google Sheets. It looks for search key “IL102b” in the range A2:A5. By default, Find is not an array formula. So we should use the ArrayFormula together with it. The Find formula here returns an array as below.

find formula returns array with value 1

This formula find a matching in the fourth row, so it returns 1 there. The match function makes use of this array. Now the search key here is 1. So the Match function would return the row number 4 and it fulfil the “row” element of the Index Function. That’s all about it.

Case Sensitive Reverse Vlookup Using Index Match

One of the main advantage of Index / Match over Vlookup is its flexibility. Let me explain you why Index_Match Combo is more flexible than Vlookup.

Index Function Syntax: INDEX(reference, [row], [column])

When you check the syntax of Index, you can understand one thing. Index returns the content of a cell by offsetting given number of rows and columns. In the syntax above, reference is the entire data range. Now take a look at the Vlookup Syntax and its comparison with colour coding.

Vlookup Function Syntax: VLOOKUP(search_key, range, index, [is_sorted])

The “Reference” in the Index Function is equal to “range” in the Vlookup. Similarly “Column” is equal to “Index”. But the “row” in the Index is not “search_key” in Vlookup. In order to make it same as “search_key”, we have to use Match function with “Row” in Index. That we have already seen in the above example.

This “Row” element of the Index function makes it flexible. How? In Vlookup we should directly input the “search_key” either within the formula or as a cell reference. But in Index, we have to find the “row” using search key that from a range. This range can be any column in your data set. But in Vlookup the search key should be from the left-most column. That’s why Index / Match is flexible and we can use it for reverse lookup.

Example to Case Sensitive Reverse Vlookup Using Index Match:

Example to Case Sensitive Reverse Vlookup Using Index Match

Here I want to find the Sales Value of one Sales Person “Ann Rivera” for the month of January, which is 3,300.  Here the name of the sales person is in the right-most column H and we need to lookup backwards in Column B or Column Index 2. The formula is as follows.

Formula: =index(A3:H8,match(1,arrayformula(find(H3:H8,”Ann Rivera”)),0),2)
Result: 3,300.00

Here A3:H8 is the reference or range, H3:H8 is the row where we can look for the search key, and 2 is the Column Index. That’s all. If you find it tough to follow, please put the same in the comments. I’ll get back to you.

LEAVE A REPLY

Please enter your comment!
Please enter your name here