Function to do a VLOOKUP in two columns

Question

I am currently stuck on a function I am working with: I wish to create a function that does a VLOOKUP on concantened Strings, then returns a numero.

Without further ado, here is my intent:

Function CusVlookup(lookupval As String, columna1 As Range, columna2 As Range, indexcol As Long)

Dim x, y As Range
Dim result As Long

For Each x In columna1
    For Each y In columna2
         If x.Value & y.Value = lookupval Then
         result = Cell(x.Row, indexcol).Value
         End If
    Next y
Next x
CusVlookup = result
End Function

So my question is: where am I wrong? Is it in the concatenation of values / comparison with a string?

Second question is: how can I work/test a Function in developpment with MsgBox & loops? Can´t find a way to do tests..

(To be totally honest, it is already a second option since I have been trying to d an INDEX-MATCH combination with Evaluate function, but after 24h of failure (even with the nemerous threads I found) I thought this might be easier, fitting my needs and actually easily re-usable.)

Thanks to whoever can help me!

`Cell` should be `Cells`: `result = Cells(x.Row, indexcol).Value` — Scott Craner, Aug 08 '17 at 17:15
It does work a bit better with an s! But still I am stuck with argument isn't optionnal at the CusVlookup = result line — draynaud, Aug 08 '17 at 18:46
Here are examples of formula that will do what you want.https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another — Scott Craner, Aug 08 '17 at 19:05
Whaow thanks, that is a lot of work you did there.. And very clever to use the sumifs for a unique occurrence. Still on issue remains: How can I address the "moving last column" issue? — draynaud, Aug 08 '17 at 19:26
do a search for x first, if x is not found then you will not find xy either, then look for y. the way you have it now, if x range is 10 elements and y range is 10 elements, then you could be doing as many as 100 comparisons. if you search x and then y, you would do 20 comparisons at most — jsotola, Aug 09 '17 at 02:12

score 0 · Accepted Answer · answered Mar 23 '18 at 19:05

I forgot to answer, sorry. I got it working:

Function double_vlookup(table As Range, p1 As Range, c1 As Integer, p2 As 
Range, c2 As Integer, indice As Integer)
Dim i, j As Integer
For i = 1 To table.Rows.Count
If table.Cells(i, c1) = p1 And table.Cells(i, c2) = p2 Then double_vlookup = table.Cells(i, indice)
Next i
End Function

Thanks to those who helped me!

Function to do a VLOOKUP in two columns

1 Answers1