Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup on first occurrence of X after Y?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup on first occurrence of X after Y?
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) jim wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup on first occurrence of X after Y?
=INDEX(B1:B14,MATCH("Q2.",LEFT(A1:A14,3),0)+MATCH( "2.",LEFT(A1:A14,2),0)-1)
ctrl+shift+enter, not just enter "jim" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup on first occurrence of X after Y?
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. Dave Peterson wrote: 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) jim wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max value for each day, and hour of that occurrence | Excel Discussion (Misc queries) | |||
Occurrence between rows | Excel Discussion (Misc queries) | |||
Vlookup based on designated occurrence of value | Excel Worksheet Functions | |||
Frequency of occurrence | Excel Discussion (Misc queries) | |||
occurrence (last) reference | Excel Discussion (Misc queries) |