Forum Discussion
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
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.
6 Replies
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.
- Your response is the best, I've mistakenly clicked to other reply.
I tried the same thing you have given but I thought that there may be some other way.You're welcome! Glad it worked as desired.
Not to worry, a moderator will notice it and fix it for you.
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
Here's a YouTube video that explains the FILTER function. https://www.youtube.com/watch?v=9I9DtFOVPIg
- the weight for truck no RJ19GF9998 is same i.e. 30 which is wrong.
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.
