Menu

VLOOKUP FOR A CASE SENSITIVE MATCH AND RETURN THE VALUE AGAINST THE FOUND MATCH AS PER REQUIREMENT

The article discusses the complete process of using this ADDIN [ It’s free] and get the desired result.

- No need of any lengthy formula. We have done that for you.
- Generalized solution. No custom solution needed.

FOR THE LEARNING VLOOKUP CLICK HERE.(FOR VLOOKUP IN HINDI CLICK HERE).

In this article we would learn to use VLOOKUP to extract the data from a table which has repeated or copy of lookup values differing only in the case.

The function to be used is GKVLOOKUP [Gyankosh vlookup].

Let us find out what it can do for us.

* *

The function is very specifically meant for the situations such as:

- When you want to retrieve the value from a column which has CASE SENSITIVE INFORMATION such as two values differing only in the case. [ Easy and easy, cat and Cat and so on].
- Only the first occurrence is important. The function has the inherent problem of the Vlookup that it’ll only provide the first occurrence available from top to bottom search.

ADDIN is like a small installation to your MICROSOFT EXCEL software which adds to the capabilities of the EXCEL.

CLICK ON THE BUTTON BELOW TO DOWNLOAD THE ADDIN FOR GKVLOOKUP FUNCTION.

AFTER CLICKING THE BUTTON ABOVE, a file named gkvlookup.xlam will ask your approval to save. Save it in the desired location.

SOMETIME DUE TO SETTINGS ISSUE IF THE BROWSER TRIES TO OPEN THIS FILE AND SHOW YOU ABSURD CHARACTERS RIGHT CLICK ON THE BUTTON>SAVE LINK AS>CHOOSE LOCATION AND SAVE THE FILE.

ADDIN is in the form of a simple excel file with a .xlam extension.

Follow the steps to install the ADDIN in Excel.

- OPEN EXCEL.
- Go to OPTIONS>ADDINS
- Select EXCEL ADD-INS
- Click GO.
- A new dialog box will open as shown in the picture containing all the EXCEL ADD-INS list.
- We can select the Addins we want to activate.
- In our case we want to install the add in , so click BROWSE.
- An OPEN FILE DIALOG BOX will open.
- Choose the file and click OK.
- The Add in will show in the list.
- Select it and click ok.
- Add in is installed.

The process is shown in the picture below.

The syntax of the GKVLOOKUP FORMULA is

**=GKVLOOKUP(COMPLETE RANGE OF TABLE, VALUE TO BE MATCHED, COLUMN TO BE RETURNED )**

Let us take the example of a coded language with the following codes.

We need to find out the corresponding numbers for the given codes from the given table.

CODES | NUMBER EQUIVALENT |

A | 1 |

a | 1.5 |

B | 2 |

b | 2.5 |

C | 3 |

c | 3.5 |

D | 4 |

d | 4.5 |

Ab | 3.5 |

AB | 3 |

cb | 6 |

Cb | 5.5 |

de | 9.5 |

From the given table, find out the code for Ab,AB,Cb,cb and c

VLOOKUP will fail in this case as it’ll always return the first instance even if the case is different but letters are same. Let us see how GKVLOOKUP performs here.

Let us find **out the numbers for the given codes.**

- Put the following formula in M6 (You can put the formula in any cell)
**=GKVLOOKUP($I$6:$J$18,L6,2)**- CLICK ENTER and 3.5 will appear as result.
- I6 to J18 is the table in which we’ll look up.
- 2 is the column index for the result to be returned.

- Hold the drag point in the right lower corner of the cell focus.
- Drag down the formula.
- The result will appear as shown in the picture below.
- We can see that the result is case specific and correct.

OTHER WAYS TO REACH THIS ARTICLE

## WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.