Match and Greater

[malbarki]

Hi guys, I hope you can help with this

I have a small table in one sheet with two columns

A B
-------------------------
2222 1
2222 2
2222 3
2223 1
2223 2
2224 Blank
2225 1

And what I want is a formula in another sheet to bring me (A) with the greater (B) value

Expected results

A B
-------------------------
2222 3
2223 2
2224 Blank
2225 1

 

[Somendra Misra]

Hi,

See the file.

Regards,

 

[Asheesh]

Hi...Does this help..

 

[malbarki]

Hi,

See the file.

Regards,

It seems fine, but how can i increase the range in sheet2? everytime I change the 7 to lets say a 100 I get "#N/A" even though I used the "Ctrl+Shift+Enter). And i need a long range as my data on a monthly basis can be up to 14,000 rows

 

[Somendra Misra]

@malbarki

Why not use a pivot table (See the file), or if you still need formula solution why not use dynamic name range for expanding data?

Regards,

 

[malbarki]

Hi...Does this help..

When I changed the value of (B7) to (5) the results gave me two (2223) record

 

[malbarki]

@malbarki

Why not use a pivot table (See the file), or if you still need formula solution why not use dynamic name range for expanding data?

Regards,

OMG I feel such an idiot, Yes I can use pivot table I guess I was so focused to get my results with a formula I totally blocked an easier approach.

Thank you very much and I'm so sorry for the trouble

 

[Somendra Misra]

It's OK M.

Welcome back.

Regards,