Match+Index or Vlookup with 2 columns as look up value? - VBA Loops (With Example)

Hi all,

Example sheet below!

I'm trying to set up a Match+Index or Vlookup loop with VBA to return Countries for First and Last names, if the Country cell is empty.

In the example:

There is Sheet1 and Sheet2.
On Sheet1 there is a table called DataTable, with First Names, Last Names and Countries, but for some names, the country cell is empty.
On Sheet2 there is another table called AidTable, and it also has First Names, Last Names and Countries and in that one, all the countries are there for all the names, but not all the names are there.

So I think I would need a loop, to go through the DataTable, and if the Country cell is not empty, then next loop, if the country cell is empty, then enter another loop, that loops through the Aid table and finds the same First Name - Last Name combination, and returns the country from the Aid table to the Data table.

The twist is that the First Names and Last Names are written correctly in the DataTable, but in the AidTable they are separated wrong.
(eg: First Name in DataTable: Adam Piotr Last Name in DataTable: Wrobel
First Name in AidTable: Adam Last Name in AidTable: Piotr Wrobel)

Practically I have a lookup table for the countries its just that the lookup value is in 2 columns, and they should be joined in a way that excel recognizes that they are the same thing.

If possible to solve without helper columns that would be the best!

Could someone help please?

Question posted on Mr.ExcelMessage board: https://www.mrexcel.com/forum/excel-...ml#post5231293

What have you got against helper columns?

It seems to me that the simplest approach is a helper column in each table that concatenates the lastname & first name. Then use the INDEX(MATCH()) formula.

Is there a reason why you've posted this in the programming/macros sub forum?

Should works:

Please Login or Register  to view this content.

EDIT: Hmm, it's working. I've added additional space, be sure that You are trying fixed version.