VLOOKUP..!?!?!?!? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

VLOOKUP..!?!?!?!?

Author

Topic

schmack
Starting Member

2 Posts

Posted - 2008-10-08 : 11:19:18

hello,

I'm tiring to use a similar function that excel has in SQL Server.

In Excel I have:

=
(
IF
(
($C$15="s");
VLOOKUP(
C27-SUM(C24:C26);
Tab.IRS2008!$C$7:Tab.IRS2008!$I$39;
$C$16+2;
-1
);
"erro"
)
)

in the Excel table I have:

THE TABLE:

nO C D E F G H
540 1 2 3 4 5 6
570 7 8 9 10 11 12
610 13 14 15 16 17 18
650 19 20 21 22 23 24
690 25 26 27 28 29 30
770 31 32 33 34 35 36
870 37 38 39 40 41 42
950 43 44 45 46 47 48

Example:

VLOOKUP(869 ; "table" ; 3)

the end result is 33 (gets the value in the line below 870 (line: 770) and the column 3 of the table)

-------------------------

VLOOKUP(871 ; "table" ; 4)

the end result is 40 (gets the value in the line below 950 (line: 870) and the column 4 of the table)

thanks for all the help...

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-08 : 11:40:24

Not sure how you need the output. This query should give you an idea on the approach though

select *,(select top 1 E from table where nO < A.no order by nO desc ) from table A

schmack
Starting Member

2 Posts

Posted - 2008-10-08 : 12:26:03

Hi and thanks!!

I'am using the expression that you gave me and but I don't know alot of things....

this is the query:
------------------------------------

SELECT NO, C, D, E, F, G, H,
(SELECT TOP (1) E
FROM OSUSR_U6O_TABLE_1
WHERE (NO < NO)
ORDER BY NO DESC) AS Expr1
FROM OSUSR_U6O_TABLE_1 AS OSUSR_U6O_TABLE_1_1

------------------------------------

with the query I get the whole table.

------------------------------------

SELECT G,
(SELECT TOP (1) E
FROM OSUSR_U6O_TABLE_1
WHERE (NO < NO)
ORDER BY NO DESC) AS Expr1
FROM OSUSR_U6O_TABLE_1 AS OSUSR_U6O_TABLE_1_1

------------------------------------

with this query I get the column G , now where or how do I get only the value that is the mach of the colmun and the line??
imagine if the NO is 850 where do I do that?

thank and sorry.... for being so new...

----------------------------------

SELECT TOP (1) NO, C, D, E, F, G, H
FROM OSUSR_U6O_TABLE_1
WHERE (NO < 570)
ORDER BY NO DESC

----------------------------------
with this query I get one (top 1) row but how can I now get the column??!??!?

I think I got it!!! :)

after 'TOP (1)..' I just put the column that I want....
--------------------------------------
SELECT TOP (1) G
FROM OSUSR_U6O_TABLE_1
WHERE (NO < 570)
ORDER BY NO DESC
--------------------------------------

thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources