Communiquez avec les autres et partagez vos connaissances professionnelles

Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.

Suivre

How to use excel to find second observation in range of cells? Note: VLOOKUP finds only first observation in range of cells

user-image
Question ajoutée par emad awad , internal audit manager & cost control manager , nile sugar
Date de publication: 2013/07/16
SIVADASAN PANTHEERADI
par SIVADASAN PANTHEERADI , Sr ADMN ( Actively looking for a NEW JOB) , DYNCORP INTERNATIONAL LLC (US COMPANY)

hai Sir, This function scans down the row headings at the side of a table to find a specified item.
when the item is found, it then scans across to pick a cell entry.
Syntax: =VLOOKUP (ItemToFind,RangetoLookin,ColumnToPickFrom,SortedOrUnsorted) The ItemToFind is a single item specified by the user.
The RangeToLookIn is the range of data with the row headings at the left hand side.
The ColumnToPickFrom is how far across the table the function should look to pick from.
The Sorted/Unsorted is whether the column headings are sorted.
TRUE for yes, FALSE for no.
Thank you Sir..

Mike Emerson Pasaron
par Mike Emerson Pasaron , Safety Officer , Arabian Petrochemical Co. (PETROKEMYA)

in the VLOOKUP formula if you put0 or TRUE in the4th parameter, it will search the nearest number, either higher or lower - which is one of the limitations of VLOOKUP.
As you said you want70 as a result, based on my understanding there are two ways to achieve this:1.
By using wildcard: =VLOOKUP("*70*",range,1,0)2.
By using INDEX+MATCH setting the3rd paramater of MATCH to "-1" or greater than Hope this helps

Evans Otalor
par Evans Otalor , Business Analyst / Facilitator , Marina Business School

What do you mean by second observation? Please include an example to clarify properly.
Meanwhile, if you meant the nth value (2nd,3rd,...nth) then the LARGE or SMALL function will be sufficient.
To find the second largest value of a range simply =LARGE(Range,2)

emad awad
par emad awad , internal audit manager & cost control manager , nile sugar

Dear participants Thanks for your efforts so far,It seems that mr Evans Otalor Is too close to answer question as I want formula results70 in below example not50 (Vlookup(a,a:b,2,0 Letter Score A50 A70 B40

kamran bashir
par kamran bashir , Assistant financial manager , Verage Ltd

If you are just after2nd observation then your best is using index and match function.

Utilisateur supprimé
par Utilisateur supprimé

If you want to find the highest (and not exclusively the2nd) value, then use the DMAX function.
In your example the formula would become: DMAX(A1:B4,B1,Z1:Z2) Selection criteria: - Z1 value: Letter - Z2 value: A The result will be70.

More Questions Like This