The only suggestion I have is to create a unique ID.
For example: UNIQUE ID
Concatenate(A1,B1) = 40CA30R2
Concatenate(A3,C3) = 40CA30B3
You need to the same in both sets of data.
I use this all the time. I not sure if this will work for you.
This is a discussion on VLOOKUP Second occurance within the Excel Questions forums, part of the Question Forums category; I am trying to find a way to return an second answer for a second occurance using a VLOOKUP function. ...
I am trying to find a way to return an second answer for a
second occurance using a VLOOKUP function. (maybe it can
be done with something else?) ex. range= A1:C3,
A1 = 40CA B1 = 30R2 C1 = TRUCK
A2 = 40CB B2 = 30A1 C2 = CAR
A3 = 40CA C2 = 30B3 C3 = TRAILER
VLOOKUP("40CA",A1:C3,3,FALSE) Returns "TRUCK"
How can I get a formula like this to return "TRAILER" the
second occurance of "40CA"?
Thanks, Theo
.
The only suggestion I have is to create a unique ID.
For example: UNIQUE ID
Concatenate(A1,B1) = 40CA30R2
Concatenate(A3,C3) = 40CA30B3
You need to the same in both sets of data.
I use this all the time. I not sure if this will work for you.
Why don't you just vlookup column B instead of A?
Perhaps the MyVlookup download will help you with this.
http://www.thewordexpert.com/downloads.htm
~Anne Troy
Not the easiest question...
Answers will depend on whether this is a 'real-time' query, or if you can process the entire data set in one batch.
If it's real-time, one approach is to insert a new column A, and use Fill to number the records from 1 to the last number. Then sort the entire set of data on Column A Descending. That will reverse the order of your data. Then the VLookup will pick up your second value first.
If this won't work becase you have more than 2 matches for each value, you could sort the data so that VLookup always gives you the right values. (VBA could be used to help with the sort if it will take processing logic).
If you can batch-process, there may be better approaches. Depending on your level of comfort with VBA, it isn't difficult to write code to do this.
See the figure.On 2002-06-13 16:13, theob wrote:
I am trying to find a way to return an second answer for a
second occurance using a VLOOKUP function. (maybe it can
be done with something else?) ex. range= A1:C3,
A1 = 40CA B1 = 30R2 C1 = TRUCK
A2 = 40CB B2 = 30A1 C2 = CAR
A3 = 40CA C2 = 30B3 C3 = TRAILER
VLOOKUP("40CA",A1:C3,3,FALSE) Returns "TRUCK"
How can I get a formula like this to return "TRAILER" the
second occurance of "40CA"?
Thanks, Theo
Microsoft Excel - aaRetrieveMultValAssocWithKey theob.xls ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
=
A B C D E F G H I J 1 40CA 30R2 TRUCK
2 40CB 30A1 CAR
5 40CA TRUCK TRAILER HELI
3 40CA 30B3 TRAILER
4 50CA 25F5 HELI
5 40CA 20P3 HELI
Sheet1
To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The above image was automatically generated by [HtmlMaker 2.14] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo
The relevant formulas are:
E2:
=MATCH(REPT("z",40),A:A)-(CELL("Row",A1)-1)
F2:
The lookup value (Key) of interest.
G2:
=IF(LEN($F$2),VLOOKUP($F$2,OFFSET($A$1,0,0,$E$2,3),3,0),"")
H2:
=IF(COUNTIF(OFFSET($A$1,0,0,$E$2,1),$F$2)>COUNTA($G$2:G$2),
INDEX(OFFSET($C$1,MATCH(G2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),
MATCH($F$2,OFFSET($A$1,MATCH(G2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),0)),"")
which is copied up to J2.
Aladin
[ This Message was edited by: Aladin Akyurek on 2002-06-14 23:46 ]
Can anyone please explain this - i have the following set of data in column A and need to 'lookup' named ranges from a different spreadsheet for values going into column B
Sales
Truck - Counter
Service
Dealer
VCM
Contract
Warranty
Internal
Total Truck Sales
Bus - Counter
Service
Dealer
VCM
Contract
Warranty
Internal
Total Bus Sales
Total Sales
Gross Profit
Truck - Counter
Truck - Service
Dealer
VCM
Contract
Warranty
Internal
Total Truck Gross Profit
Bus - Counter
Service
Dealer
VCM
Contract
Warranty
Internal
Total Bus Gross Profit
Other Income(Expenditure)
Price Variance
Surcharges
Total Gross Profit
Total Department Expenses
Net Profit Contribution
As you can see, for example, VCM occurs 4 times - is this possible?
NB there are about 10 of these datasets on the same sheet (dataset being the detail in italics)
Last edited by Bubba; Mar 9th, 2009 at 01:01 PM. Reason: additional info
Is this what you are looking for?
Vlookup - Return All matches
Not really.
To elaborate, columns B,C and D contain Actual, Budget and Last Year data
There are 10 regions in the company and each region is on Sheet 1, one below the other - they all contain pretty much the rows I listed above.
In it's current state, the report is unworkable and not user-friendly... I'm creating a report with each Region on a separate Sheet in another spreadsheet, let's call it Book2.xls
The report will download in a similar fashion each time it is run, only the data and time frame will change although the number of rows may differ, hence the need for a lookup of some sort.
On Book2.xls will be 10 sheets... the aim was to have all data that is not zero showing. If the revenue streams in Column A had unique names it would be straightforward then it would be fine but in it's current state, the same reference is repeated up to 4 times in each region so ideally i would need a lookup which says something like... for the first time you find the term "Service" display x, for the second, display y etc etc
Does this make sense???
Would really appreciate any pointers as i can't think of another way around this due to the format of the original report - am happy to introduce Names but don't want to cut and paste the original download if poss
Thanks!
Hi! Anne
Your Link is not Working
Hardeep kanwar
Like this thread? Share it with others