Vlookup help

[Lasantha]

Dear All,

Kindly look into the attached template. Is it possible to lookup only claims which comment equal to "AAA" to B column.

thank you.

 

[ashish1974]

does this work for you?
=INDEX(A:A,MATCH("AAA",K:K,0),1)

 

[Lasantha]

does this work for you?
=INDEX(A:A,MATCH("AAA",K:K,0),1)

thanks for your reply, but this doesn't work for me.

Kindly check the attachment for example.

 

[ashish1974]

is that that you want to put comment "AAA" in column B only against those claims (in column A) which have comment "AAA" under column K ?

 

[Lasantha]

I just want to search claims which has comment "AAA" only.

 

[pecoflyer]

In B2 and pull down =IF(INDEX($J$2:$K$9,MATCH("AAA",$K$2:$K$9,0),1)=$A2,"AAA","")

 

[Lasantha]

In B2 and pull down =IF(INDEX($J$2:$K$9,MATCH("AAA",$K$2:$K$9,0),1)=$A2,"AAA","")

hi,

thanks for reply. this is perfectly working when one claims has comment "AAA", but there can be more "AAA" with other claims IDs as well. kindly look into the attached template.

Could you please give me another formula.

thanks for your time.

 

[Peter Bartholomew]

Why not look up the comment by claim number and then test
= IF( VLOOKUP(claim, Table1, 2) = "AAA", "AAA", "" )

[Note: I prefer XLOOKUP and output via dynamic array but that is specific to Excel 365]

 

[Lasantha]

Why not look up the comment by claim number and then test
= IF( VLOOKUP(claim, Table1, 2) = "AAA", "AAA", "" )

[Note: I prefer XLOOKUP and output via dynamic array but that is specific to Excel 365]

thank you.