# Vlookup with an if (find next occurrence based on other criteria)

#### colmodoc

##### New Member
Hi all.
I would like to do a Vlookup which relies on an outside condition (colour in this example).
As follows:

 B C D E F G H ​ ​ ​ ​ ​ ​ ​ ID Colour Value ​ Lookup Value Vlookup Desired result 101 red ​ ​ 103 lookup(f5,B:D,2,0) red 101 blue ​ ​ 103 lookup(f5,B:D,2,0) blue 102 red ​ ​ ​ ​ ​ 102 blue ​ ​ ​ ​ ​ 103 red ​ ​ ​ ​ ​ 103 blue ​ ​ ​ ​ ​ 104 red ​ ​ ​ ​ ​ 104 blue ​ ​ ​ ​ ​

<tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>
Thanks for looking
Colm.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### BarryL

##### Well-known Member
This is an array, enter with ctrl+shift+enter

=INDEX(C:C,SMALL(IF(B:B=F5,ROW(C:C)),ROW()-4))

ROW()-4 if you are entering in G5

#### colmodoc

##### New Member
Thanks Barry. Would you mind giving a brief description of what that's doing? Much appreciated.

#### BarryL

##### Well-known Member
best way to view how a formula is working is to go to the formulas tab on the ribbon and click "Evaluate Formula".

Index returns a value at a point of intersection (Array, row number, col number). SMall returns the nth smallest number in a data set.

Here our array is C:C as this is the area where our output lies.

next we need to get the row number of our output on row C. But we need to add a condition in as we only want colours that have the same id numbers.

So our condition is the if. It assigns a true/false value to all cells in col B whether it is equal to F5. If true it is given the row its row number , if false it gets the value false.

the row-4 is for the nth or kth number. 1 will give you the smallest, 2 the second smallest , 3 the third smallest etc. this allows us to get the second instance.

Thank you Barry.

Replies
20
Views
755
Replies
3
Views
418
Replies
0
Views
123
Replies
14
Views
1K
Replies
1
Views
342

1,191,723
Messages
5,988,307
Members
440,148
Latest member
sandy123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back