I have 2 tabs in my sheet, Main_Tab and Value_Tab.

They look like this:

Main_Tab

Value_Tab

Fiddle

Now the day is something I never know in advance, the user puts in the date and based from that date the A row (day row) fills in that day according to the entered date.

What do I want to achieve:

?A should display Value_Tab's B4 cell as in row 4 is the zipcode, and its a Mo so its column 2
?B should display Value_Tab's C4 cell as in row 4 is the zipcode, and its a Sa so its column 3
?C should display Value_Tab's D4 cell as in row 4 is the zipcode, and its a Su so its column 4

I sas thinking in this direction but I'm using Excel expressions in VBA which of course won't work but I just want to show in what direction I'm thinking.

Sub CheckPrice()
    If Range("A2").Value == "Mo t/m Fr" Then
        =VLOOKUP(A4,Tarieven!ENTIRESHEET:ENTIRESHEET,2,0)
    ElseIf Range("A2").Value == "Sa" Then
        =VLOOKUP(A4,Tarieven!ENTIRESHEET:ENTIRESHEET,3,0)
    Else Range("A2").Value == "Su" Then
        =VLOOKUP(A4,Tarieven!ENTIRESHEET:ENTIRESHEET,4,0)
    End If
End Sub

I hope I posted this right and that I'm clear.

CodePudding user response：

I have created a new macro which may do the job.

Sub get_price()

Dim wb As Workbook, ws As Worksheet, data_ws As Worksheet
Dim cur_row As Integer, max_row As Integer, data_column_index As Integer

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Main_Tab")
Set data_ws = wb.Sheets("Value_Tab")

max_row = ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

With ws
    For cur_row = 2 To max_row
        Select Case .Cells(cur_row, 1).Value
            Case "Mo", "Tu", "We", "Th", "Fi"
                data_column_index = 2
            Case "Sa"
                data_column_index = 3
            Case "Su"
                data_column_index = 4
        End Select

        .Cells(cur_row, 3).Formula = "=index(" & data_ws.Name & "!" & (Columns(data_column_index).Address) & ",match(" & .Cells(cur_row, 2).Address & "," & data_ws.Name & "!$A:$A,0))"
   Next
End With
End Sub

I inject formula into the target cells only because you were inserting Vlookup formulas; otherwise, I would do the lookup right in VBA and get the value. To do that, you can replace the line

       .Cells(cur_row, 3).Formula = "=index(" & data_ws.Name & "!" & (Columns(data_column_index).Address) & ",match(" & .Cells(cur_row, 2).Address & "," & data_ws.Name & "!$A:$A,0))"

with

        With Application.WorksheetFunction
            ws.Cells(cur_row, 3) = .Index(data_ws.Columns(data_column_index), .Match(ws.Cells(cur_row, 2), data_ws.Columns(1), 0))
        End With

which will input the value in the Price column instead of formulas.

Also, to follow up on my question if you are willing to spread the Mo to Fr columns, I have put together a sample file so you can look at the result. The Excel sample file contains 4 tabs. 2 tabs for the VBA solution, and 2 tabs for the formula solution with Mo to Fr spread out, using combination of index() and match() function.

The index() function works by taking in the Lookup Array, Row index, then Column index; like a coordination on a map.

The match() function is a very efficient lookup formula which can match horizontally and vertically. The best part, there is no need to sort the data on the source array.

Combining the two functions can get what you want from a data table, much, I really mean much, more efficiently than vlookup() and hlookup().

Another method is using sumproduct() formula.

I have included all those in the sample file helpfully can help you to understand each method. Regardless, your last choice would be vlookup and hlookup as they are very CPU intensive once your sheet is fill with lots of lookups.

Sample Excel File