Forum Discussion

Debs_au's avatar
Debs_au
Copper Contributor
Aug 31, 2020

Help needed to convert XLookup formula to VBA code

I solved my problem with XLookup looking up more than 1 string (ie look for both a given name and a surname, and return the record using the formula below in a cell in the spreadsheet, but I have no idea how to write it as VBA code.

With the given name in cell A1, and the surname is in cell B1.

The column to lookup to find the given name is Column F

The column to lookup to find the surname name is Column G

And then return the whole record on the row (columns A to V) if there is a match

With an error message "Record Not Found" if a match cant be found.

=XLOOKUP(A1&B1,F:F&G:G,A:V,"Record Not Found")

In my macro, I use input text boxes on a user-form "I-Given" and "I_Surname" but I have no idea how to convert the excel formula into VBA code.

 

No RepliesBe the first to reply