vlookup on first occurrence of X after Y?

[jim]

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.

 

[Dave Peterson]

Maybe...

with A1 holdinq Q2 (say)

=match(a1,sheet2!a:a,0)
to return the row of the first match.

To get the value in column B for this match:
=index(sheet2!b:b,match(a1,sheet2!a:a,0))

to get the value in column B for the row under that row:
=index(sheet2!b:b,match(a1,sheet2!a:a,0)+1)

to get the value 2 rows under that match:
=index(sheet2!b:b,match(a1,sheet2!a:a,0)+2)

 

[Teethless mama]

=INDEX(B1:B14,MATCH("Q2.",LEFT(A1:A14,3),0)+MATCH("2.",LEFT(A1:A14,2),0)-1)

ctrl+shift+enter, not just enter

 

[Dave Peterson]

After reading Toothless Mama's post, I see that my initial interpretation of the
layout of your data may have been incorrect.

I thought that
Q2 would be in column A
and
What's your favorite food?
would be in column B.

If that's not correct, the formulas I suggested could have a wildcard added:

=match(a1&"*",sheet2!a:a,0)
and
=index(sheet2!b:b,match(a1&"*",sheet2!a:a,0))
and so forth.