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..
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
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)
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
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.