VLOOKUP - Multiple Criteria

[gregula82]

Hi Everyone,

I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

Any ideas???

Thanks.

Greg.

 

[Adamdwebb]

Try this really useful user defined function I stumbled across as a potential solution

[INDENT]Function ThreeParameterVlookup(Data_Range As Range, Col As Integer, Parameter1 As Variant, Parameter2 As Variant, Parameter3 As Variant) As Variant

Dim Cell
Dim Current_Row As Integer
Dim No_Of_Rows_in_Range As Integer
Dim No_of_Cols_in_Range As Integer
Dim Matching_Row As Integer
 
ThreeParameterVlookup = CVErr(xlErrNA)
Matching_Row = 0
Current_Row = 1
No_Of_Rows_in_Range = Data_Range.Rows.Count
No_of_Cols_in_Range = Data_Range.Columns.Count
 
If (Col > No_of_Cols_in_Range) Then
ThreeParameterVlookup = CVErr(xlErrRef)
End If
If (Col <= No_of_Cols_in_Range) Then
Do
If ((Data_Range.Cells(Current_Row, 1).Value = Parameter1) And _
(Data_Range.Cells(Current_Row, 2).Value = Parameter2) And _
(Data_Range.Cells(Current_Row, 3).Value = Parameter3)) Then
Matching_Row = Current_Row
End If
Current_Row = Current_Row + 1
Loop Until ((Current_Row = No_Of_Rows_in_Range) Or (Matching_Row <> 0))
If Matching_Row <> 0 Then
ThreeParameterVlookup = Data_Range.Cells(Matching_Row, Col)
End If
End If
End Function
[/INDENT]

Sorry about the formatting, first ever post!! Hope this proves useful to somebody somewhere. ray:

 

[kterkuile]

Ooooh, that looks promising for something i'm working on. Any clue as to how it works?

Take care,

Karel

 

[Adamdwebb]

Hi Karel,

This function works like a VLookup (obviously I guess!) but the columns I think must be adjacent and in the correct order of search (ie. parameter 1 should be found in the first column and so on) and text string searches are case sensitive.

HTH

 

[kterkuile]

Hi Adam,

Thanks for the reply. Andrew Poulsum solved my problem with this solution: http://www.mrexcel.com/forum/showthread.php?p=2329977#post2329977.

Had fun with the function though!

Take care,

Karel

 

[Adamdwebb]

Hey Karel,

Glad to hear you got it sorted in the end, apologies that I wasn't more useful at any stage! Thanks for posting that thread for me, I'm sure that that will come in useful very soon!

 

[kterkuile]

apologies that I wasn't more useful at any stage

Don't apologise; i'm actually very grateful for your input. It opened my eyes to a whole new way of looking at the problem. I tried to alter the function (and failed miserably LOL) before Andrews brilliant response, but even trying taught me a lot. So, thank you so much for helping me out.

Take care,

Karel

 

[mummbles74]

I am using this formula {=INDEX(D1:D500,MATCH(1,(A1:A500=E5)*(B1:B500=F5)))} to try and return a value that matches 2 criteria but all it ever returns is the number 1.
I have column 'A' containing the month (repeated each time for every day in the month). Column 'B' contains the date (1-31 etc) and in column 'C' I have my work trip number, which is a 14 day pattern (again the number is repeated for every 'date' that it is the answer for, wasn't sure if merging the cells would work with this formula)
I want to be able to type the month in cell E5 and the date in cell F5 and have the formula return my working trip number. any ideas.
I also tried using multiple Vlookup's like someone else stated in this thread but have had no luck with that either.

regards

mummbles74

 

[kterkuile]

I'd use the 'SUMPRODUCT' link i've posted before. Put the formula in the cell where you want your working trip number to appear and make the 'entry cells', the cells that you want your ranges to be equal to. It works absolutely fine. Don't forget to thank Andrew!

Take care,

Karel

 

[mprovince]

I'm looking for something similar and this thread looked like I may get what i'm looking for.

I use a department code to differentiate between teams and have one type customer satisfaction score for one department (on tab called csat1 for example) and another type of score for a different department (on tab called csat2).

So the need is this - if you select an employee in dept "a" then the scorecard will show csat data from the tab csat1. Then if you select an employee from dept "b" the scorecard will show csat data from tab csat2.

thanks in advance

 

[travisc]

The formula worked for me also, but I have 2100 rows, and it constantly freezes up recalculating all of it... haha...