How to use bottom up lookup
Requirement: We have 2 tabs. Tab 1 column A has a list of stores. For each store in Column A, I need a function to search and get the Region from Tab -2 in column B. In Tab -2 the Regions come 1st and then the stores. So the lookup should be bottom to top.
Tab1
| Str0001 |
| Str0002 |
| Str0003 |
| Str0004 |
| Str0005 |
| Str0006 |
| Str0007 |
| Str0008 |
| Str0009 |
| Str0010 |
| Str0011 |
Tab -2
| REG_0001 |
| Str0001 |
| Str0002 |
| REG_0002 |
| Str0003 |
| Str0004 |
| Str0005 |
| REG_0003 |
| Str0006 |
| Str0007 |
| Str0008 |
| Str0009 |
| REG_0004 |
| Str0010 |
| Str0011 |
The expected result in column B
| COL A | COL B |
| Str0001 | REG_0001 |
| Str0002 | REG_0001 |
| Str0003 | REG_0002 |
| Str0004 | REG_0002 |
| Str0005 | REG_0002 |
| Str0006 | REG_0003 |
| Str0007 | REG_0003 |
| Str0008 | REG_0003 |
| Str0009 | REG_0003 |
| Str0010 | REG_0004 |
| Str0011 | REG_0004 |
Solution: B2 =LOOKUP(2,1/(LEFT(OFFSET(Sheet2!$A$2,0,0,MATCH(A2,Sheet2!A$2:A$35,0)),3)="REG"),OFFSET(Sheet2!$A$2,0,1,MATCH(A2,Sheet2!A$2:A$35,0)))
Obtained from the OzGrid Help Forum.
Solution provided by pike.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to Lookup between a starting word and a finishing word |
| How to use cell content as input to a structured reference as part of a lookup function |
| How to use VLOOKUP (example) |
| How to use VLOOKUP (example) |
| How to use advanced lookup: Multiple criteria when looking up values in a table |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
