Forum Home
Press F1
Thread ID: 67256	2006-03-22 09:44:00	Excel VLookup Array help	Mike (15)	Press F1

Post ID	Timestamp	Content	User
439894	2006-03-22 09:44:00	I am trying to get a vlookup to work using an array... however the name of the array is in another cell. Is it possible to get excel to look at the other cell and get the value of that cell as the array name? eg array name = Mike. Cell A1 = Mike in b2 is the lookup = vlookup(b1,a1,1,false) where a1 needs to be the value in a1, not the range a1... Surely this is possible? :) Mike.	Mike (15)
439895	2006-03-22 10:54:00	Youve lost me Mike. Vlookup has these arguments... VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) In your example the first argument you have is B1 and the second is A1 so this is saying lookup value of B1 and see if its in A1. Perhaps you mean to have somthing like this where B1:B100 are the cells your looking for the text "Mike"... =VLOOKUP(A1,B1:B100,1,FALSE)	Parry (5696)
439896	2006-03-22 11:52:00	Youve lost me Mike. Vlookup has these arguments... VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) In your example the first argument you have is B1 and the second is A1 so this is saying lookup value of B1 and see if its in A1. Perhaps you mean to have somthing like this where B1:B100 are the cells your looking for the text "Mike"... =VLOOKUP(A1,B1:B100,1,FALSE)I want the specify the named array using the value in A1... so if the value of A1 = "Mike" then the named array = "Mike". If the value of A1 = "Parry" then the named array = "Parry". What I have is multiple named arrays, and multiple rows to lookup in my named arrays, each row has the name of the named array it needs to look in, so I want to tell it which named array to use by looking in a certain cell in each row. Unfortunately the way vlookup works is by using either a cell range or named array, so it won't look at the value of a cell for the named array... Mike.	Mike (15)
439897	2006-03-22 17:07:00	Got ya. You have defined several names in the workbook with each representing a different range of cells. You want to enter the name of the range as text in cell A1, then refer to cell A1 to get the range of the defined name. The INDIRECT function allows you to enter a cell address reference or a defined name reference as text and return the range involved. So using your original formula of vlookup(b1,a1,1,false) you would enter... =VLOOKUP(B1,INDIRECT(A1),1,FALSE). You will need to be careful that the size of the named range is big enough to accept it as defining the data range for vlookup else you will get a #REF! error. For example, if A1 contains the named range Mike which represents C1:C100 then if you want to return the value of column E (ie entering 3 as the 3rd argument) this will not work as the range would need to be C1:E100. hth	Parry (5696)
439898	2006-03-24 00:04:00	=VLOOKUP(B1,INDIRECT(A1),1,FALSE).Easy as that :) Thanks heaps - this has saved us a lot of time. Cheers, Mike.	Mike (15)
439899	2006-03-24 06:49:00	Good Grief! MS think of everything. Isn't that elegant.	Mike S (1766)
1