| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vertical Lookup from a List
Both worked! Thanks!
-- BDG "Arvi Laanemets" wrote: Hi "B Golden" wrote in message ... Got it... Cell A1 is the pull down referring to a named list on a separate sheet. To make it simple, say the list contains A, B, C & D, which also is the name of the array that I want to call up. If I choose B, I want my formula to read VLOOKUP(A4,A1,2) and then refer to named array B to get my answer. The syntax for VLOOKUP is : VLOOKUP(LookupValue,LookupRange,ColumNumber,LookFo rNearest) LookupValue is the value, the function is looking for in leftmost column of LookupRange; LookupRange is a contignous range, which must start with lookup column, and contain return column; Column number is the relative position of return column in LookupRange (lookup column number is 1) LookForNearest determines the behaviour of function when no exact match is found. When LookForNearest is False or 0, the exact match is searched for, regardless of lookup column order. When no matching valie is found in leftmost column, and #N/A error is returned; When LookForNearest is True or 1, and the lookup table is sorted by leftmost column, the nearest match for LookupValue is returned. When the table is unordered, the function behaviour is not predictable; VLOOKUP(A4,A1,2) doesn´t have any sinnvoll meaning and does return an error (because lookup range is a singel cell, you are looking for a value A4 in cell A1, and are attempting to return a value from second column of this cell !!!). OK. Let's quess what you did mean at all. You have some named ranges (A, B C and D), with at least 2 columns in any of them, are you? Depending on selection in data validation list in A1, you want to look for value in cell A4 in leftmost column of one of those named ranges, and return according value from second column of same range. Am I right so long? On fly: =VLOOKUP(A4,CHOOSE(MATCH(A1,{"A";"B";"C";"D"},0),A ,B,C,D),2,0) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Relative Value to a Vertical Lookup Result? | Excel Worksheet Functions | |||
| lookup? list data into summary table | Excel Worksheet Functions | |||
| Vertical Lookup problem | Excel Worksheet Functions | |||
| How to lookup the dates of a list that are only the first of the . | Excel Worksheet Functions | |||
| Lookup closest number in list | Excel Discussion (Misc queries) | |||
Hybrid Mode
