vlookup 2nd instance

[dragon]

I am writing a documentation tool for vmware and have the following in worksheet "vm partition Data"


Server Name, Disk, Capacity MB,

Bob, Hard Disk 1, 2048

Bob, Hard Disk 2, 4096


On the first worksheet I am using the vlookup function as follows

=VLOOKUP(Working!$D$8,'vm partition Data'!B1:V219,2)


Looking up a drop down list of the server names.

However I am struggling to return the second match of Bob.


To be clearer, in Cell D16 I would like Hard Disk 1, E16, 2048

in Cell D17 I would like Hard Disk 2, E17, 4096

 

[sdsurzh]

Hi Dragon,


Since both the names are Bob the vlookup will take the first instance.


Any Expert can give the clear idea about this.(Even i want to learn about this)


Thanks,

Suresh Kumar S

 

[Faseeh]

Hi dragon,


If your data is present in cell A1:C3, use this formula and press ctrl+shift+enter and drag down:

=INDEX($C$1:$C$2,SMALL(IF($A$1:$A$14="Bob",ROW($A$1:$A$14)),ROW(A1)),0)

This will give you all the matches for Bob, you can replace bob in formula with some cell reference.


Regards,

 

[kaushik03]

Hi Dragon,


Just wanted to explain the logic behind the formula posted by Faseeh above:


Step 1 - Identify cells where match occurs


Let’s examine $A$1:$A$14="Bob" part:


(Equal sign) is a comparison operator and checks if criterion ("Bob") is equal to values in array ($A$1:$A$14)


Write the following formula in any cell and press F9


$A$1:$A$14="Bob" becomes:


={FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}


Since in the range ($A$1:$A$14) bob is present in second and third position, the second and third value becomes ‘true’


[If you would have bob in any other cells within this range, true/false would change accordingly]


Step 2 – Need to create array with row numbers


Write the following formula in any cell and press F9


ROW($A$1:$A$14) becomes:


={1;2;3;4;5;6;7;8;9;10;11;12;13;14}


Step 3 - Filter row numbers that match our criteria


=INDEX($C$1:$C$2,SMALL(IF({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},ROW({1;2;3;4;5;6;7;8;9;10;11;12;13;14})),ROW(A1)),0)


Becomes:

INDEX($C$1:$C$2,SMALL({2,3}),ROW(A1)),0)


The above part of the formula returns the k-th smallest number in the array


ROW(A1) is used to create the number 1 which is referred as k-th smallest number


Say you write the formula at E2, when you copy the formula down to cell E3, ROW(A1) changes to ROW(A2). ROW(A2) is 2.


So at E2 the k-th smallest number from the array would be 2 and at E3 the k-th smallest number from the array would be 2


Now finally through INDEX formula we fetch the required value:

INDEX($C$1:$C$2,{2,3})


At col C the K-th smallest number returns the value 2048 (for row2)


Hope this helps.


@Faseehlz correct me in case I have explained something wrong above.


Kaushik

 

[bobhc]

Good day dragon


The uploaded may help you


https://dl.dropbox.com/u/34893656/Correct_Formula-IfError_with_Vlookup.xlsx

 

[dragon]

Due to having an old brain I had to use a combination of all three answers for it to finally sink in.


=IFERROR(INDEX('vm partition Data'!D1:D215,SMALL(IF(Working!$D$8='vm partition Data'!$A$1:$A$215,ROW('vm partition Data'!A1:A215)),COLUMN(B$1))), "")/1024


Above was the final formula for showing the size of the 2nd match in GB rather than MB..


I tip my hat to the three of you.

Thankyou!!