#1
|
|||
|
|||
Variable Table_Array in VLOOKUP
Hi.. Using Vlookup I can find the data I'm looking for in a sorted list. I then want to find the next occurrance of the same data so in effect I want my VLOOKUP table_array to be altered to start one row after where the original data was found. I may then want to find a third instance of the same data so the table_array range would need to start one row later again. Can you give me some pointers on how would I do this?
EG - If I use: =vlookup($a$1,Data!$A$1:$D$50,1,false) to find my target string and it finds it in the 'Data' sheet in row A27 how would I find the next occurrance without manually altering my function to =vlookup($a$1,Data!$A$28:$D$50,1,false)? (That is A27+1 so that it starts looking in the remainder of the table_array to find any other instances.) Thanks in advance. Last edited by pdab09; 11-18-2010 at 02:19 AM. Reason: Icons appeared in text! |
#2
|
|||
|
|||
The easiest way to do this is to use a helper column. Sort your data in Col A (data Sheet) so that the replications are next to each other.
Then insert a couple of columns before A. In what is now Col B insert =If(c2=c2,max($c$1:c2)+1,1) Then in what is now col A simply join B&C =b2&c2 This will give you a unique ref for each entry and then from this you can use your vlookup to search this unique ref to get the answers you need. If you need a bit more help post an example and I will make it work for you Matthew |
#3
|
|||
|
|||
Thanks Matthew but making a unique entry for each of the original targets won't work for me.
The target string I am looking for is supplied from a dropdown list (with unique entries) elsewhere in the spreadsheet. What I'm aiming for is to select the target string from the dropdown and my first vlookup will find and select the data for the first entry (this much is straight forward). In the next row I then want to dynamically alter my Table_Array range to find the next instance of the same target string, and maybe the next again and so on until I have retrieved all entries of the target string. That way all of the entries in the list will be returned for the want of making the first dropdown selection - regardless of how many entries are in the list in the first place. |
#4
|
|||
|
|||
This should work (I did lift this from RagDyer in another forum)
I have attached a simple workbook so you can see it working. You will need to chage cell references etc but thats quite easy. Say your list is on Sheet1 Ensure that the drop down box is in A1 on sheet 2 Then in A2 of Sheet2, try this *array* formula: =IF(COUNTIF(Sheet1!B$1:B$5,A$1)>=ROWS($1:1),INDEX( Sheet1!A$1:A$5, SMALL(IF(Sheet1!B$1:B$5=A$1,ROW($1:$5)),ROWS($1:1) )),"") -- Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *After* the CSE entry, copy down as far as you anticipate the number of returns you'll get. Make sure you copy down enough rows to ensure *all* possibilities are displayed. Last edited by Matthew; 11-23-2010 at 06:59 AM. Reason: correction |
Tags |
table_array, variable, vlookup |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using Vlookup & IF together | thelauncher | Excel | 5 | 08-25-2013 11:32 PM |
how to make fields calculation variable according to the filter i use | duraid | Project | 0 | 09-09-2010 01:05 PM |
Share VBA variable in multiple templates | shameerau | Word VBA | 0 | 07-05-2010 08:37 PM |
Inputting variable data | axy | Word | 0 | 09-08-2009 04:50 PM |
Find and Replace - Variable change | Ssendam | Excel | 1 | 07-16-2009 11:44 AM |