Excel: How to choose data on two separate rows in the same column

[Guest]

Hello,

I have data that has the same NAME repeating on two separate rows of the
same column; the NUMBER column is different. Here is an example of what this
data,
located in Tab C of my spreadsheet, might look like:

COL A: NAME COL B: NUMBER
D0_DIMM_CLK0_A_H 4.1506626
D0_DIMM_CLK0_A_H 4.714883
D0_DIMM_CLK0_A_L 4.1341208
D0_DIMM_CLK0_A_L 4.7194365

As you can see, the data in COL A repeats, but the data in COL B is different.
In a separate Tab, call it Tab A, of my spreadsheet, I have set up a formula
that needs to use the 1st instance of this data; to do this search, I use the
following
formula:

=VLOOKUP(A1,TabC!A:B,2,FALSE)

where A1 has the value "D0_DIMM_CLK0_A_L" in it. So this works perfectly
fine.
The value returned is 4.1506626.

In Tab B of the spreadsheet, I have another set of data that need to use the
*second* value of the instance of "D0_DIMM_CLK0_A_L"; in other words, I need
to use the 4.714883 value. However, if I try using the same VLOOKUP equation
I used above, I'll still get the 4.1506626 value.

What can I do to specify I want to skip the first instance of the data, and
find and use the data from the second instance of this data? I'm not sure
how the INDEX or MATCH functions could help here.

Certainly, I could split up the two instances of the data to occur on two
separate columns, but the problem with that approach is that I have a lot of
data that is constantly changing and it would be extremely difficult to go
split it all up each time the data is updated.

Any help with this task will be greatly appreciated.

Thanks!

rei.

 

[Biff]

Hi!

Is there always a second instance and might there be a third, fourth, fifth?

Can't use entire columns as references:

=LOOKUP(2,1/(TabC!A1:A5=A1),TabC!B2:B5)

This will find the LAST instance, be it the second, third, fourth, etc.

Biff

 

[Guest]

Hi,

This works very well, for what I need to do. Thank you very much!

rei.