Microsoft Office Forums Variable Table_Array in VLOOKUP

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-17-2010, 02:48 AM
pdab09 pdab09 is offline Variable Table_Array in VLOOKUP Windows XP Variable Table_Array in VLOOKUP Office 2007
Novice
Variable Table_Array in VLOOKUP
 
Join Date: Nov 2010
Posts: 6
pdab09 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 11-22-2010, 10:41 AM
Matthew Matthew is offline Variable Table_Array in VLOOKUP Windows XP Variable Table_Array in VLOOKUP Office 2007
Novice
 
Join Date: Nov 2010
Posts: 6
Matthew is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 11-23-2010, 03:07 AM
pdab09 pdab09 is offline Variable Table_Array in VLOOKUP Windows XP Variable Table_Array in VLOOKUP Office 2007
Novice
Variable Table_Array in VLOOKUP
 
Join Date: Nov 2010
Posts: 6
pdab09 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 11-23-2010, 05:28 AM
Matthew Matthew is offline Variable Table_Array in VLOOKUP Windows XP Variable Table_Array in VLOOKUP Office 2007
Novice
 
Join Date: Nov 2010
Posts: 6
Matthew is on a distinguished road
Default

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
File Type: xls Multi Returns.xls (29.0 KB, 8 views)

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

Tags
table_array, variable, vlookup

Thread Tools
Display Modes


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
Variable Table_Array in VLOOKUP Find and Replace - Variable change Ssendam Excel 1 07-16-2009 11:44 AM


All times are GMT -7. The time now is 01:00 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft