We help IT Professionals succeed at work.
Get Started

How do I get back values from vlookup's N/A cells?

Last Modified: 2017-03-18
Hi Experts,

I have data in four columns in an Excel sheet. Column A and D represent Names while columns B and E represent their corresponding amounts. I want to be able to get out all names that are not in both name columns with their corresponding amount for further processing.
So far, I have used the vlookup function that has given me those names that are not in both columns A and D with N/A where there is no match. (This is just mock up data, I have to do this in a file of more than 65,000 records)
Now I want to:
  1. Sort these values in the vlookup column to get all N/A to the bottom.
  2. Then get back the actual values of these N/A cells and their corresponding amounts for further processing

So, in my example, I am more interested in getting back the following values:
Allan                  243.52
David                 203.05
Fred                   175.25
Nelson               135.22
Noah                  89.85
Pascal                 147.41
Peter                  156
Theresa              189.34

How do I achieve that?

Thanks & Regards,
Watch Question
Excel VBA Developer
Distinguished Expert 2018
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE