Vlookup - Duplicate Values

**sraju** · 11-23-2010, 04:22 PM

HI,

This is my first posting and i have very limited knowledge in writing Excel formulas. Here is my question

Col1 Col2 Col3
------- ------ -------
AAA 1 Grapes
BBB 2 Banana
TEST 3 Apple
TEST 4 Orange

How do I write a excel formula to Capture the value "Apple". I used the Vlookup Formula "=VLOOKUP("TEST",A1:C4,3)" and it not working the way I wanted. My Result is always "Orange". What is the formula that I can write to capture Apple in one row and than another formula to capture Orange in another row.

Please Note: My Values in the Table are getting loaded dynamically(number of rows change)

Thanks
Sraju

**Paul** · 11-23-2010, 07:31 PM

Hi Sraju, welcome to the forum.

To always return the first match in VLOOKUP, you must use the 4th argument of VLOOKUP and set it to FALSE. So change your formula to:

=VLOOKUP("TEST",A1:C4,3,0)

Where 0 is equivalent to FALSE. This should return the first match. You can't, however, use VLOOKUP in one instance to return the first match, and in another find the last match.