• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Criteria Vlookup Function

K

Kinghart

Member
Hi… Need help in this…

Suppose I'm having some numbers in columns A,B,C. In colum A I have 2,5, 10 In B I have 3, 5, 26 and in C I have 6, 8, 12

What I want to do is In D1, when I say "B" and E1, "4" F1 should tell me if the number 4 is in the column B numbers and return True if true and false if false.

Thanks In Advance
 
Hi ,


Try the following formula and see.


=IF(ISERROR(VLOOKUP(E1,INDIRECT(INDIRECT("D1")&":"&INDIRECT("D1")),1,FALSE)),FALSE,TRUE)


This can probably be improved , but you can see if it works.


Narayan
 
Narayan's Logic is spot on, although it can be shortened somewhat by this change:

=NOT(ISERROR(VLOOKUP(E1,INDIRECT(CONCATENATE(D1,":",D1)),1,FALSE)))


Not only is this a shorter formula but, because INDIRECT is a volatile function and CONCATENATE is not, the fewer uses of INDIRECT we have the faster this will calculate. In all honesty for the 3 rows in the sample this is negligible, but it's always best practise to minimise the volatility in any worksheet formula.

Regards,

Pob
 
Kinghart,

With the exact layout you stated in first post,

[pre]
Code: 
2	3	6
5	5	8
10	26	12
[/pre]
pob's formula evaluates to TRUE if the value of 3, 5, or 26 is entered in D1. Is there something different in the layout than what you told us?


PS. Just because there's always a different formula that does the same thing, here's a slightly shorter formula:

=ISNUMBER(MATCH(E1,INDIRECT(D1&":"&D1),0))
 
Thanks guys.... Maybe I wasn't very good in asking the question... You see, the top row is actually A B and C... ie. Products A B and C... and from A2 the numbers begin... So how to put the formula??
 
Hi ,


Assume A1 contains "A" , B1 contains "B" and C1 contains "C".


Assume your numbers are in A2 , B2 , C2 , A3 , B3 , C3 ,...


Assume you enter the column to be searched ( A , B or C ) in cell D1 ; assume you enter the number you want to find , in cell E1.


Luke's formula [ =ISNUMBER(MATCH(E1,INDIRECT(D1&:&D1),0)) ] , gives the correct result. If the entered number is found in the search column , it returns TRUE , otherwise it returns FALSE.


You can enter the formula in any unused cell.


Are you getting anything else ?


Narayan
 
Hi ,


Can you verify whether the numbers that are in columns A , B and C are actually numbers ?


In any used cell , enter =A2+B2 and see whether it gives you the correct result.


Narayan
 
The Actual Thing I Want To Do IS I Want (B5) To Say True When It Is A Holiday From The List Below.. And I Put (I56)"Jul-Aug" And write down the holidays in that time (I57 to I67) 15, 18, 20 …. Next in (J56)"Aug-Sep" And Write Down The Holidays In That Time And So On....

In (I2) Is The Month I Select From I5 Are The Days (1 to 31) Of The Month

This Is The Formula I'm Using And Works Perfectly But I want An Easy / Short One

=IF($I$2="Oct-Nov",NOT((ISNA(VLOOKUP(I5,$L$57:$L$70,2,0)))),IF($I$2="Nov-Dec",NOT((ISNA(VLOOKUP(I5,$M$57:$M$64,2,0)))))) And So On…
 
Hi ,


Thanks for the details.


If I understand you fully , your data starts from I56 ( "Jul-Aug" ) , and goes on to J56 ( "Aug-Sep" ) , K56 ( "Sep-Oct" ) ,...


Below I56 , J56 , K56 ,... are the numbers 15 , 18 , 20 , signifying holidays in that period.


In I2 , you will enter any one of Jul-Aug , Aug-Sep , Sep-Oct ,....


From I5 down through I6 , I7 ,... till I35 , you will have the numbers 1 through 31.


Against these numbers , you want , in the cells J5 through J35 , either TRUE or FALSE to appear.


Is this correct ?


Or , is it that in I5 , you will enter a number between 1 and 31 ; in B5 you want either TRUE or FALSE to appear , signifying whether the date entered in I5 is a holiday in the period entered in I2 ?


Narayan
 
Hi ,


Can you put in the following formula in J5 , and copy it down till J35 ?


=ISNUMBER(VLOOKUP(I6,OFFSET($I$56:$I$87,0,MATCH($I$2,$I$56:$L$56,0)-1,COUNT($I$56:$L$87)),1,FALSE))


I5 through I35 contain the numbers 1 through 31.


I have used I87 , J87 , K87 ,... as the final cells in your list of holidays , just to make things easier.


Narayan
 
You must log in or register to reply here.
Back
Top