Vlookup

[CARFL97]

I'm having a difficult time with a Vlookup formula and any help would be
greatly appreciated!

When using a Vlookup statement, and there is more than one match, how can
the formula be modified to list the next match?

For example, I have two individuals that currently have the same result,
however the vlookup formula only returns the first match and I am attempting
to rank 1-5.

 

[JLatham]

Explain more about your data and perhaps someone can help with getting the
ranking done for you.

As for VLOOKUP() [and LOOKUP() and HLOOKUP()] they all stop with the first
match that they find.

You might look at the RANK and/or the PERCENTRANK functions.

 

[Shane Devenshire]

Hi,

Try something on the order of:

=OFFSET($H$1,LARGE((A$20=B$2:B$17)*ROW(B$2:B$17),ROW(A1))-1,0)

This would be an array formula which means you enter it by pressing
Shift+Ctrl+Enter, not Enter.

In this case the name you are looking for is in A20 and it could be anywhere
in B2:B17. There are titles on the first row. In this case I was looking up
the values in column H. Drag the formula down as far as necessary, it will
return all hits.

 

[Hardeep Kanwar]

Hello Sir,

I am facing same Problem, I try your Formula but it shows #REF!

Sheet2
Phone Number
26423694
4030609646
4069934154
8025350322
8025350322
8472258299
8474204342
8856237979
9000556977
9000556977
9000766578
9003293914
9008354129
9008416704
9008448709
9018098525
9030161071
9030353805
9030481623
9030483779
9030483779

Sheet3

Phone Number Preferred Campus
2331772 Bangalore
22247264 Chennai
24030404 Bangalore
26423694 Chennai
4030609646 Hyderabad
4069934154 Hyderabad
4442992167 Chennai
8025350322 Bangalore
8025350322 Punjab
8472258299 Hyderabad
8474204342 Hyderabad
8856237979 Vishakapatnam
9000091997 Hyderabad
9000556977 Hyderabad
9000556977 Pune
9000766578 Hyderabad
9003293914 Chennai
9008354129 Bangalore
9008416704 Bangalore
9008448709 Hubli
9018098525 Bangalore
9030161071 Hyderabad
9030353805 Hyderabad
9030481623 Hyderabad
9030483779 Hyderabad
9030483779 Delhi

Right Now i am using this
OFFSET(Sheet3!$B$1,LARGE((A$2=Sheet3!A$2:A$27)*ROW(Sheet3!A$2:A$27),ROW(A1))-1,0)

Confirm with CTRL+SHIFT+ENTRE

Where i was Wrong

Thanks in advance