MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLOOKUP - using two variable fields


Posted by Connie on June 15, 2001 6:35 AM

I have a user form that I want VLOOKUP to fill-in based on two fields which can vary. My fields (columns) are: Political Issue Name, Issue Explanation, Our Candidate's Viewpoint, Our Candidate's Voting Record, Opponent's Name, Opponent's Viewpoint, Opponent's Voting Record.
The first field that can vary on the user form is Political Issue Name. The issue is pulled onto the user form from selections made by user on a previous sheet. So, on the user sheet, the Issue Name might be IssueA or IssueB or IssueC, etc.
The next field that can vary is Opponent's Name. This is done with a drop-down list so that the user can choose which opponent they want to examine. From here, I'd like the user form to automatically fill-in the remaining fields such as voting record, candidate's position, etc.
This is where I'm stuck. I can use VLOOKUP by putting Issue Name in the first column, but how do I accommodate the opponent's name which can change? I could put each opponent's name in separate columns, but then, how in the formula do I tell it which column to look in since the user can choose any one from the drop-down list? I also tried to use Match and Index but I couldn't get that to work either (mainly because I don't know what I'm doing!).
Any ideas appreciated,
Connie

Posted by Aladin Akyurek on June 15, 2001 7:17 AM

Connie,

The answer depends partly on how you organized your data. Send or post a (cooked up) snippet if you wish.

Aladin

Posted by AB on June 15, 2001 7:45 AM

There are some multi-lookup examples on my website that show how you can use concatenation to combine multiple fields for various types of LOOKUP formulas. Basically you create a new field that is a concatenation of two other fields and use that as the lookup criteria field.

Regards,
AaronThe Excel Logic Page