VLOOKUP Second occurance

Thanks:  0
Likes:  0

1.
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
.

2. 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.

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

5. 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.

6. 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.

 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
 E2G2H2I2J2 =

A
B
C
D
E
F
G
H
I
J
1
40CA30R2TRUCK

2
40CB30A1CAR
540CATRUCK TRAILER HELI
3
40CA30B3TRAILER

4
50CA25F5HELI

5
40CA20P3HELI

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.

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.

[ This Message was edited by: Aladin Akyurek on 2002-06-14 23:46 ]

7. ## Re: VLOOKUP Second occurance

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)

8. ## Re: VLOOKUP Second occurance

Originally Posted by 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)
Is this what you are looking for?

9. ## Re: VLOOKUP Second occurance

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!

10. ## Re: VLOOKUP Second occurance

Hi! Anne

Hardeep kanwar

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•