Alternate Vlookup formula

**mangesh** · 07-23-2010, 01:50 PM

Hi All,
I need a function which will give me the data from other column based on a criteria.
Example:
Say in Column A i have different IDs and in Column B i have the records. Column A may contain duplicate IDs also. Now i want a function which will give me the result in Column C. Say in Cell A1 the ID is ABC123 and in Coumn B the corresponding record is PQR123.
Again in Cell A5 i have the same ID ABC123 and in Column B the corresponding record is XYZ345.
Now i want a function which will return me unique record from Column B for ID ABC123.
When i use vlookup funtion it is giving me the same result PQR123 both the time.

Is there any function which will give me PQR123 for the first search on my ID and XYZ345 for second search?

**Whizbang** · 07-23-2010, 02:03 PM

This will help you.

http://office.microsoft.com/en-us/ex...001226038.aspx

The formula you are looking for is

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

This is an array formula, which means you cannot simply press the enter key to confirm. You must confirm with ctrl+shift+enter.

It is also important to note that array formulas cannot be run on entire rows or columns (ie A:A) They need to be defined as finite ranges (ie A1:A25). You could go with A1:A65536, but your formula would process very slowly. One way around this is Dynamic Named Ranges. Do a search and you should find plenty of explaination on those.

**mangesh** · 07-23-2010, 02:16 PM

Hi Whizbang,
Thanks for your help.
Will you please let me know what is the significance of $a$10 in this formula. I tried your formula but its not working.

**Whizbang** · 07-23-2010, 02:40 PM

$A$10 is simply the value you would like to match. This formula is copied exactly from the website I linked. In that site, A10 was simply the cell they were comparing.

Using your situation described above, I would designate, say, D1 as your "Variable" cell. Meaning we will search column A for all records that match the value of D1

So, I would then paste into E1 this formula

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$1,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$D$1,ROW($A$1:$A$7)),ROW(1:1)),2))

Then I would copy that formula down as far as necessary. As you copy the formula, Excel will change the bit "Row(1:1)" to "Row(2:2)" and so on. Thereby giving you subsequent matches to your D1 value.