Using VBA's to Eliminate N/A's in a vlookup/index match function

This thread from last week shows how it can be done with 3 basic formulae:

http://www.excelforum.com/excel-form...xed-value.html

Hope this helps.

Pete

Thank you, I tried doing the above but when I use the data validation by consultant I get a really long list, as a consultant could have worked on more than one client, and I'm not sure how to approach my spreadsheet with your method. I attached a sample file just to give you an idea. There is way more data than that.

Thank you for your help.

SSample Experience.xlsx

The data validation should be driven from a separate list, which is just a unique list of consultants. I'll set this up for you in your sample file when I've have a coffee break, so I'll post back later.

Pete

Okay, I've added two new sheets to demonstrate this to you. In the Ref_data sheet I've listed the 5 consultants and set up a named range covering Ref_data!$A$2:$A$6. If you have more consultants in your real file then you can add them to the bottom of the list, but you must also adjust the named range. This list is shown in the drop-down in cell C1 of the Select sheet - just click on the down-arrow to select the consultant of interest, and the data will adjust automatically.

I've sorted your data in Sheet2 in a different order, so that the names were not all bunched together - it doesn't matter what order the records are in. I've added a formula to column H of this sheet which is copied down way beyond your actual data (it doesn't matter how far, but the hyphens are there to help show you). This formula identifies records which meet the criteria (i.e. contains the name of the consultant chosen on the Select sheet), and sets up a unique sequence for those records. Note that the name can be part of a multiple entry in the consultants column.

In the Select sheet I have also used column H as a helper - this formula finds the row(s) where the records identified on Sheet2 occur. Then I have a straightforward INDEX formula in the main body of the sheet, to retrieve the appropriate data from the main list. You can hide column H on both sheets if you don't want to see them.

As I said earlier, just choose a consultant from the list in C1 and the data will change automatically.

I've left your other sheets in the file, though I don't really see a need for individual sheets for each consultant, now that you can just select one from the list.

Hope this helps.

Pete

Pete,
I'll take a look! Thank you so much for your prompt response and help. I'll keep you posted

Pete,
I'll take a look! Thank you so much for your prompt response and help. I'll keep you posted

S

Pete,

I implemented your method in my original file, it works wonderfully!! It was so good to actually solve this without having to use code. Thank you again!

s

You're welcome - thanks for feeding back.

Perhaps you can mark the thread as solved if you think it has been (click on Thread Tools above your first post).

Pete