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

colmodoc

New Member
Joined
May 20, 2009
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
Thanks Barry. Would you mind giving a brief description of what that's doing? Much appreciated.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top