Vlookup Help - Need 2 Criteria [SOLVED]

[Nikki91775]

Hi Everyone,


I am working on an excel spreadsheet where I need to lookup the value of 2 items. For example, I need to lookup everything that is a .25 for sales by the month, 1, 2, 3, 4, 5 and so on. So my vlookup is this:


=VLOOKUP(A4, Data!, MATCH('By Pump Size'!O2, Data!AK:AL, 2, False)),


A4 is my .25", the Data! is where my pivot table resides. By Pump Size!02 is where my month resides - say month 1.


I cannot get this to work. It keeps telling me I have an error in the formula. This is my first try at trying to get 2 values. Any help is appreciated.


Thanks.

 

[NARAYANK991]

Hi Nikki ,


Two things are wrong with your formula :


1. The syntax of the MATCH function


2. The syntax of the VLOOKUP function.


The MATCH function always returns a number , indicating the position of the element which matches the lookup value ; in your case , 'By Pump Size'!O2 will contain the numbers 1 through 12. The second parameter of the MATCH function should be a one-column range or a one-row range ; in your case , you are specifying Data!AK:AL , which is a two columns range. The MATCH function will not work.


Again , the MATCH function has 3 parameters , the last one being optional ; it can take the values -1 , 0 or 1. In case you don't specify it , the default value is 1.


So , even if you want to change the way the MATCH function has been written , you will need a closing parenthesis after specifying the range as Data!AK:AV , as follows :


MATCH('By Pump Size'!O2, Data!AK:AV)


where the range AK:AV is 12 columns for the 12 months


Even with this change , the VLOOKUP function will not work , since the MATCH function returns a number , whereas the VLOOKUP function expects a range in the second parameter.


Lastly , the 2 needs to be replaced by the column where the month matches.


You can try something like this :


=VLOOKUP(A4, Data!AK:AV, 'By Pump Size'!O2, False)


where the range AK:AV is 12 columns for the 12 months ; the value in A4 will be matched with the values in column AK , and the corresponding value from the month column will be returned.


Narayan

 

[Nikki91775]

Hi - I tried the formula and it still does not work.


In my pivot - I have each size .25, .50, .75". Then I have months 1 - 5. For this year only. I have sum of extended quantity and I have sum of extended price.


Here is the formula that I used and it only returned .25" instead of my quantity.


=VLOOKUP(A4, Sheet1!A:F, 'By Pump Size'!O2, FALSE)

 

[Nikki91775]

Hi - I actually got it playing around with it. I needed to specific in the match where to look.


=VLOOKUP(A4, Sheet1!A:F, MATCH( 'By Pump Size'!O2, Sheet1!A$9:F$9, 0), FALSE)


Thanks for all your help.

 

[NARAYANK991]

Hi Nikki ,


Can you copy paste the following :


1. Contents of A4


2. The range Sheet1!A1:F20


3. Contents of 'By Pump Size'!O2


If O2 contains 1 , then the VLOOKUP function will return the value 0.25 ; if you want the values from columns B , C , D , E and F returned , change the formula to :


=VLOOKUP(A4, Sheet1!A:F, 1 + 'By Pump Size'!O2, FALSE)


I assume that data for month 1 is in column B , data for month 2 is in column C and so on.


Narayan

 

[jason]

it may be helpful if you could upload a sample file?


I think it has something to do with your lookup value and table array...

does Col.A contain the the ".25" that youre looking for?

does 'By Pump Size!O2 contain a valid column number value?

i'm not sure if you're dragging this formula to surrounding cells, but if you are, you may want to make the references Absolute.