Excel Formula: An Array formula containing VLOOKUP, SMALL, IF, OR

[albert276]

I am trying to create a formula that considers the data from three columns Y, H and V on my tab titled 'Data', returning the smallest value in A that meets that condition, once found Vlookup that smallest value and return the value from B. Below is what I have so far, and it is working until I try to add in my last condition.

I am looking to change the IF(Data!V:V="4") function with an OR function, I would like to turn the v:v="4" into " V:V="4" or V:V="5" ", when I tried to include an OR function the formula started ignoring the V:V condition and starts returning only if the Y and H conditions are met.

Below is the working formula with one V:V condition working without the OR function. Thank you in advance for any help.

=VLOOKUP((SMALL(IF(Data!Y:Y="3",IF(Data!H:H=3,IF(Data!V:V="4",Data!A:A))),1)),Data!A:B,2,0)

 

[NARAYANK991]

Hi Albert ,

Firstly , avoid entire column references , where possible.

Try this :

=VLOOKUP((SMALL(IF(Data!Y1:Y5000="3",IF(Data!H1:H5000=3,IF(Data!V1:V5000={"4","5"},Data!A1:A5000))),1)),Data!A1:B5000,2,0)

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

 

[JAMIR]

Hi Albert ,

Firstly , avoid entire column references , where possible.

Try this :

=VLOOKUP((SMALL(IF(Data!Y1:Y5000="3",IF(Data!H1:H5000=3,IF(Data!V1:V5000={"4","5"},Data!A1:A5000))),1)),Data!A1:B5000,2,0)

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

Hello Narayan Sir,

As you see my excel file SIMS related to Gate Pass Number problem. Sir, this file size is 4 MB. And as you see this file contain macro, vlookup, index match fuction, Pivot Table. Now this file is taking to time for calculating the formulas. My work is suffering very badly. My voucher work are now manually.

Sir, could you help me that this file formulas would be work very fine. My system confg. core2due & 2 GB RAM. File containing 12000+ rows data.

What the solution? Now i am unable to upload this file due to large fiel.

Warm Regards,

Zameer Shaikh

 

[Somendra Misra]

Hi Zameer,

If you have a problem, try to create you own thread, don't hack into other's threads. As this will cause problem to the persons who search for similar problem on the forum.

Regards,

 

[JAMIR]

Hi Zameer,

If you have a problem, try to create you own thread, don't hack into other's threads. As this will cause problem to the persons who search for similar problem on the forum.

Regards,

Hello Sir,

Sorry sir, I am posting a new threads.

Regards,

Zameer Shaikh