Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP when a range resides in Column A
I'm quite familiar with the various LOOKUP functions, but do not know how to
do this: Column A has a list of zip codes (first three numbers only) and Column B has a value associated with the zip code. The data in Column A can either be three digits (908) or a range of zip codes (908-909) and represented as I have shown. What function is used to LOOKUP the value in Column B when the input the user will provide will only be the first three digits of the zip? If the data in Column A were only three digits for every row (908), that is straightforward. Not sure how it works if Excel needs to analyze the input to determine if the corresponding value resides on a row where a range (908-909) is present. TIA Rich |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP when a range resides in Column A
With value in C1 e.g. 908
=INDEX($B$1:$B$100,MATCH(C1,LEFT($A$1:$A$100,3),0) ) Enter with Ctrl+Shift+Enter HTH "rpalarea" wrote: I'm quite familiar with the various LOOKUP functions, but do not know how to do this: Column A has a list of zip codes (first three numbers only) and Column B has a value associated with the zip code. The data in Column A can either be three digits (908) or a range of zip codes (908-909) and represented as I have shown. What function is used to LOOKUP the value in Column B when the input the user will provide will only be the first three digits of the zip? If the data in Column A were only three digits for every row (908), that is straightforward. Not sure how it works if Excel needs to analyze the input to determine if the corresponding value resides on a row where a range (908-909) is present. TIA Rich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
XL 2007: Pivot Refresh slow when data resides in another workbook | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
CountIf first column range = "Word" and second column range <> 0 | Excel Worksheet Functions |