May 19, 2009 at 2:10 pm
In Excel, the HLookup function searches for value in the top row of table_array and returns the value in the same column based on the index_number.
The syntax for the HLookup function is:
HLookup( value, table_array, index_number, not_exact_match )
value is the value to search for in the first row of the table_array.
table_array is two or more rows of data that is sorted in ascending order.
index_number is the row number in table_array from which the matching value must be returned. The first row is 1.
Iwould like to do same thing using TSQL. Any suggestions? Thanks in advance!!
May 19, 2009 at 2:34 pm
[font="Verdana"]Excel can treat a range of cells like it is a table. SQL Server has (inherently) tables. So you don't have to pretend that a range is a table. Excel doesn't distinguish between meta-data (for example, column names) and data (actual values): these are all plonked into cells regardless. SQL Server, on the other hand, knows the difference.
HLOOKUP() restricts a search for a matching value to a column, based on a value in a row. So really it's doing two things:
1. looking up the value in the row to match. Oddly, this is usually the name of the column. So in reality, this is selecting the column to search.
2. looking down a list of values (in the column selected initially) to find the value from the row number corresponding to the specified parameter.
It does some weird things though. For example, when you look up the column name, if it can't find an exact match it will use the value prior. In the Excel example, this means that "B" will match to "Axels", not to "Bearings" or "Bolts".
Anyway, I can't think of why you would want to generate exactly the same behaivour. You could write some dynamic SQL that searches the table meta-data to do it if you really need to. But if all you need to do is search a column in a set for a value, you can use query similar to the following:
with OrderedTable as (
select *, row_number() over (order by MyColumn) as RowNumber
from dbo.MyTable
)
select MyColumn
from OrderedTable
where RowNumber = {value};
I think I have the matching logic! Bit rusty with Excel.
If you let us know what you are actually trying to do, we may be able to give you a better idea of how to go about it.
[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply