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,