Vlookup and match right to left

I would like a formula to lookup and match from right to left
Please see attachment.
Many thanks
Ian BellretiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:

You can't use VLOOKUP for that but you can use a combination of INDEX/MATCH:

=INDEX($D$2:$D$16, MATCH(A2,$G$2:$G$16,0))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Ian,

Few suggestions other than the formula suggested by Norie.

1) To hide the formula error in case if a name in column A is not found in column G, use the formula with IFERROR like below.
=IFERROR(INDEX($D$2:$D$16, MATCH(A2,$G$2:$G$16,0)),"")

Open in new window

2) For matching names, if the Color is blank in column D, the formula in column B will return 0 which you may not want. In that case, select the formula cells and custom format them using the format 0;-0;;@, this will hide zeros from the formula cells.

To apply the custom formatting, select the formula cells --> right click on any of the selected cells --> choose Format Cells --> On format window, on Number Tab, click on Custom and replace General in Type Box with 0;-0;;@
Ian BellretiredAuthor Commented:
Hi Neeraj,
I like your addition to Norie's suggestion. I will try and add that. Meanwhile can you please have a look at a question I posted subject title
Many thanks
Ian BellretiredAuthor Commented:
Thanks Norie, works fine and Neeraj's addition makes it perfect.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Ian!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.