Creating VBA Vlookup for 3 columns

Question

Hi ALL,

Can anyone help me out creating a vlookup in macro? Two reasons that i need it to be macro instead of normal formula.

• I need to have empty fields in the exceltable itself and where i got 3 columns that need to be autofilled by vlookup if one of the fields is filled in manually.
• Also i need to get rid of an itterations error as the following formula =if.error(Vlookup(C5,'Sheet2'!A:C;2;False),"") in an example in cell B5 looks up in it's own cell if there's an error and then fills nothing instead of n/a or 0
  See my onedrive link for testfile: https://1drv.ms/x/s!AvZDqUtNeJmX5FkoeAg1g9Lwkzzq?e=BMslmK as i'm unable to upload a macro enabled excel file

Basically sheet 1 contains 4 columns where i need to be able look up the addtional data from the table in sheet 2 when some data is fillled

I've found a script / tweaked it a bit that helps me in my way, but not entirely.

Right now i got 2 problems with the vlookup macro:

1. i need to have the macro input the result instead of the formula into the cells.
2. i get an error trying to automate the script to trigger the macro on change in column C, instead of a button.

attached the examplefile with the macro i got so far. (see also below)

Sub Auto_Open()
Dim lrData As Long

lrData = Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Row

With Sheets("Sheet1")
.Range("D2:D" & .Range("C" & Rows.Count).End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(C2, 'Sheet2'!C$2:E$" & lrData & ",3, FALSE) , """")"
End With

End Sub

Private Sub Worksheet_Calculate()
'Updateby Extendoffice
Dim Xrg As Range
Set Xrg = Range("C2:C29")
If Not Intersect(Xrg, Range("C2:C29")) Is Nothing Then
Auto_Open
End If
End Sub

Answer 1

Re: find a value?

I looked at your onedrive workbook, but it wasn't much help.
My free "Custom_Functions" Excel add-in might work. One of the 20+ functions included is the X_vLOOKUP function.
=X_vLOOKUP(LookupValue, DataRange, LookupCol, GetFromCol, OccurrenceNum, LookupValue2, LookupCol2)

It can return multiple values as you fill down (the OccurrenceNum can be a row reference that increments).
It can use any column(s) on the worksheet and the last two optional arguments provide a second lookup.
The first column is Column(1) of the DataRange. The first column to the left is Column 0. the next one is column(-1) and so on.
There is also a separate Word.doc examples document available and the add-in includes a cheat sheet.

Download from OneDrive: https://1drv.ms/u/s!Au8Lyt79SOuhZ_zj8wEtOjDcqAI?e=MrzfpA

'---
NLtL
Calculate Payments, Custom_Functions, Greatest Films, Professional_Compare

Answer 2

Hi @Arnoud Holtzer

Tag “office-excel-itpro” focuses on general issues about Excel clients.
Based on your description, your issue is more related to macro, I will remove the tag.
Thanks for your understanding.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

A workable sollution was given by "PCI" On this forum:

https://www.excelforum.com/excel-programming-vba-macros/1337748-creating-vba-vlookup-for-3-columns.html