Index and match

[GN0001]

I have a table as below:


1695 BA UU

1439 PR UU

1413 BA UU

1529 BA UU

1224 BA UU

1413 BA UU

1695 BA UU

1096 BA UU

1224 BA UU

1413 BA UU

1439 BA UU

1411 PR UU

1411 PR AA

1412 BA UU

1224 PR AA

1439 BA ZZ

1413 PR AA

1413 PR AA

I entered the index formula as below and my rows extends to row 396(I can't bring all the rows here:


=INDEX($G$2:$I$396,MATCH($I2,$I$2:$I$396,0),1)

The first search value UU retrieves 1695, which is correct.

The second search Value UU retrieves 1695, which is not. It needs to retrieve 1439.


It brings the first matching value.


I need the second row bring the value in second row.


How can I resolve this?


Regards,

Guity

 

[Hui]

Guity

Have a read of http://chandoo.org/wp/2010/11/10/vlookup-second-value/

and associated comments where there are several more suggestions

 

[GN0001]

Hui,

Thank you very, very much. I used the helper column and countif,it worked great. I didn't try other ways. Which one do you suggest is the best if you have time to answer this question?


By the way, I was able to figure out the dynamic dashboard, ch6 sheet. The only thing is left on dynamic dashboard is user form and one more question. I will attend to them later.


Regards,

Guity