I've been searching prior questions, but I can't find this exact situation.

I'm trying to set up a vlookup formula that looks in a different workbook,
finds the first occurrence of X after Y and returns the value some number of
column(s) to the right (the desired column will change depending on the
specific project). There are multiple occurrences of X, but Y is unique.

The data structure will look something like this:

Column A Column B
Q1. What's your favorite color?
1. Red 10%
2. White 15%
3. Blue 30%

Q2. What's your favorite food?
1. French fries 35%
2. Hot dogs 40%
3. Apple pie 45%

Q3. What's your least favorite food?
1. French fries 66%
2. Hot dogs 47%
3. Apple pie 51%

So for instance, if the above was the data workbook, I might want to write a
formula that looks for Q2, then finds the value for response 2 and returns
40%. I can't simply do a vlookup on "2. hot dogs" because that same value
recurs in a different question.

Just to complicate matters further, the data in the lookup array (i.e.,
column 1 in the data workbook) will likely include both letters and numbers
in the same cell, though maybe with a modified vlookup, that's not a problem,
as I can duplicate the same string in the other workbook.


I hope this is clear enough - please let me know if clarification is
required. Thanks very much for your help.