Unusual Lookup Table
I think I need VB code for a lookup table with non-unique entries in the match column.
Below is an example. I am given the LOOKUP TABLE and the values in the GIVEN column.
I want to fill the DERIVED column as shown. Note that each time a value in the Lookup Table is used, it may not be used again. For example, the first entry "d" gets a DERIVED value of "10" from the LOOKUP TABLE. The next occurence of "d" may not use "10" again. It gets "11" from the LOOKUP TABLE.
Hope that makes sense. Thank you!
Dave
| LOOKUP TABLE | GIVEN | DERIVED | ||
| a | 1 | d | 10 | |
| a | 2 | b | 5 | |
| a | 3 | d | 11 | |
| a | 4 | c | 8 | |
| b | 5 | a | 1 | |
| b | 6 | a | 2 | |
| b | 7 | f | 14 | |
| c | 8 | e | 13 | |
| c | 9 | f | 15 | |
| d | 10 | b | 6 | |
| d | 11 | |||
| d | 12 | |||
| e | 13 | |||
| f | 14 | |||
| f | 15 | |||
| f | 16 | |||
Dave, you can do this with
Dave, you can do this with formulae
conceptually - retrieve the row of the first instance of "d" using the MATCH formula... then use VLOOKUP on the range that occurs after that cell...
Construct the new vlookup range by using the OFFSET function