SOLVED

# Vlookup second, third, fourth & so on values

Brass Contributor

# Vlookup second, third, fourth & so on values

Vlookup

How find second, third, fourth & so on values whose occurrence has more than 1 may be 10 times?

sheet attached

6 Replies

# Re: Vlookup second, third, fourth & so on values

You didn't say which of the several sets of values you wanted to use as the reference. And there are several that could qualify.

So it's hard to know exactly how to help. IF what you're wanting to find is all the occurrences of a given truck number, the FILTER function would work, as in

=FILTER(H2:J31,H2:H31="RJ19GF9998")

if, that is, you have Excel 2021 or newer on your system

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Vlookup second, third, fourth & so on values

One simple approach is, insert a helper column A and place the following formula in A2 and copy it down.

In A2

``=C2&"-"&COUNTIF(C\$2:C2,C2)``

And then use the following VLookup formula to get the desired output...

In K2

``=VLOOKUP(I2&"-"&COUNTIF(I\$2:I2,I2),\$A\$2:\$E\$31,5,0)``

Refer to the attached with formulas in place.

# Re: Vlookup second, third, fourth & so on values

the weight for truck no RJ19GF9998 is same i.e. 30 which is wrong.

# Re: Vlookup second, third, fourth & so on values

I tried the same thing you have given but I thought that there may be some other way.

# Re: Vlookup second, third, fourth & so on values

You're welcome! Glad it worked as desired.

Not to worry, a moderator will notice it and fix it for you.

# Re: Vlookup second, third, fourth & so on values

Apparently, I misunderstood the original question. "Weight" didn't appear to have anything to do with it. The way I read the question, he was trying to find all of the occurrences of something -- "How find second, third, fourth & so on values" -- and then, since VLOOKUP had been his reference, as VLOOKUP does, find other associated values.  FILTER does that. But it apparently is not what he was seeking after all.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Vlookup second, third, fourth & so on values

One simple approach is, insert a helper column A and place the following formula in A2 and copy it down.

In A2

``=C2&"-"&COUNTIF(C\$2:C2,C2)``

And then use the following VLookup formula to get the desired output...

In K2

``=VLOOKUP(I2&"-"&COUNTIF(I\$2:I2,I2),\$A\$2:\$E\$31,5,0)``

Refer to the attached with formulas in place.