VLOOKUP Second occurance

[theob]

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
.

 

[Ninnett]

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.

 

[Parra]

Why don't you just vlookup column B instead of A?

 

[Anne Troy]

Perhaps the MyVlookup download will help you with this.

http://www.thewordexpert.com/downloads.htm

 

[DZarlengo]

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.

 

[Aladin Akyurek]

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

See the figure.

aaRetrieveMultValAssocWithKey theob.xls
	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
...

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

 

[Bubba]

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)

 

[Aladin Akyurek]

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)

Is this what you are looking for?

http://www.mrexcel.com/forum/showthread.php?t=231882

 

[Bubba]

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!

 

[hardeep.kanwar]

Hi! Anne

Your Link is not Working

Hardeep kanwar