Challenging questions on vlookup and index match

[rohan jha]

Dear All,

Please look the excel first.

Now in a column i have the "Values" and i have to fetch the name. So the look up value is 22 and the name that i need to fetch is "A".

But as 22 is used twice, the vlookup is always fetching "A". What i need is that first time it should return A while next time it should search and then return "F".

Please suggest a solution.

 

[Hui]

Rohan

Firstly, Welcome to the Chandoo.org Forums

I assume you want this as an answer:


In G2:
=IF(COUNTIF(A:A,$E$2) < ROWS($E$2:E2),"",INDEX(B:B,SMALL(IF($A$2:$A$8=$E$2, ROW( $A$2:$A$8)),ROW(A1)))) Ctrl+Shift+Enter

Then Copy G2 down

You can read about how this works here:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

See your sample file below:

 

[Nebu]

Hi:

Non array solution.

=INDEX($B$2:$B$8,AGGREGATE(15,6,ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1/($A$2:$A$8=$C$2),ROW(A1)))

Drag the formula down.

Thanks

 

[bosco_yip]

Another non-array formula option.

Using Hui's table, in G2, formula Copy down :

=IFERROR(INDEX(B$2:B$8,MATCH(0,INDEX(COUNTIF(G$1:G1,B$2:B$8)+(A$2:A$8<>E$2),0),0)),"")

Regards
Bosco

 

[Hui]

Bosco

My table in G2:G10 isn't a fixed Table
It is the answer to Rohan's original question
That is, it is the values in column B that match when Column A = E2 = 22

 

[bosco_yip]

Bosco
My table in G2:G10 isn't a fixed Table
It is the answer to Rohan's original question
That is, it is the values in column B that match when Column A = E2 = 22

Hi Hui,

My formula returned result as same as yours.

Maybe this part…(A$2:A$8<>E$2)… made difference from your formula....(A$2:A$8=E$2)...

In fact, the formula comprised with Remove Duplicate formula (Red) + Conditional matching (Blue)

=IFERROR(INDEX(B$2:B$8,MATCH(0,INDEX(COUNTIF(G$1:G1,B$2:B$8)+(A$2:A$8<>E$2),0),0)),"")

The Conditional matching part is : ….(A$2:A$8<>E$2)….

Then, how does it work ?

1] .....(A$2:A$8<>E$2)….. return {FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE} ….. >> {0;1;1;1;1;0;1}

>> A$2:A$8<>22 (un-matching) return FALSE (0),
and, A$2:A$8=22 (matching) return TRUE (1)

2] …..MATCH(0,INDEX(COUNTIF(G$1:G1,B$2:B$8)+
(A$2:A$8<>E$2),0),0) …..>>

>> ….MATCH(0,{0;1;1;1;1;0;1},0) …..>>

and 0 inside the Lookup array {0;1;1;1;1;0;1} become the marching value in respect of the MATCH Lookup criteria 0.

>> Return 1

3] =IFERROR(INDEX(B$2:B$8,MATCH(0,INDEX(COUNTIF(G$1:G1,B$2:B$8)+(A$2:A$8<>E$2),0),0)),"") …..>>

>> =IFERROR(INDEX(B$2:B$8,1),"") …..>>

>> Final return "A"

Regards
Bosco