Microsoft Office Forums Variable Table_Array in VLOOKUP
 Register FAQ Search Today's Posts Mark Forums Read

#1
11-17-2010, 02:48 AM
 pdab09 Windows XP Office 2007 Novice Join Date: Nov 2010 Posts: 6
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.)

Last edited by pdab09; 11-18-2010 at 02:19 AM. Reason: Icons appeared in text!
#2
11-22-2010, 10:41 AM
 Matthew Windows XP Office 2007 Novice Join Date: Nov 2010 Posts: 6

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
11-23-2010, 03:07 AM
 pdab09 Windows XP Office 2007 Novice Join Date: Nov 2010 Posts: 6

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
11-23-2010, 05:28 AM
 Matthew Windows XP Office 2007 Novice Join Date: Nov 2010 Posts: 6

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.
Attached Files
 Multi Returns.xls (29.0 KB, 8 views)

Last edited by Matthew; 11-23-2010 at 06:59 AM. Reason: correction

 Tags table_array, variable, vlookup

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post thelauncher Excel 5 08-25-2013 11:32 PM duraid Project 0 09-09-2010 01:05 PM shameerau Word VBA 0 07-05-2010 08:37 PM axy Word 0 09-08-2009 04:50 PM Ssendam Excel 1 07-16-2009 11:44 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 10:48 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top