How do perform l excel lookup function in SQL

Question

0.00/5 (No votes)

See more:

How do i perform excel lookup function in sql.
As per my understanding i can use where condition for searching a value in table as it works as lookup when exact values matched, Here the scenario is different in excel when we perform lookup operation if the looking value is not available in target column it will pick the least value from the same column

EX:- I want to look this value 33.33% in cola and need to return value from colc as 13
in excel am getting correct result. but how do i perform this operation in sql server query

Problem no 1)33.33% not lies in cola values

Problem no 2)How do i pick 13 value from colc , when 33.33% near to 33.42% then i will get 14 from colc

COLA      COLB       COLC
33.06%	  1.06	      13
33.42%	  1.07	      14
33.78%	  1.08	      15

What I have tried:

i tried using where condition

SQL

select colc from tablename where cola=33.33

SELECT  sum(colc)/3 From tablename where round(COLA,0)=33.00 -- randomly tried

Posted 19-Jun-16 21:14pm

Burning Thoughts

Updated 19-Jun-16 22:23pm

Tomas Takac

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-06-19T22:23:00

Using this example table

SQL

create table tablename
(
	COLA      float,
	COLB       float,
	COLC int
)
insert into tablename values
(33.06,	  1.06,	      13),
(33.42,	  1.07,	      14),
(33.78,	  1.08,	      15)

Probably the easiest way to get the data you want is to order the table by the difference between your input value and COLA. Then just pick the top row returned. I.e.

SQL

declare @lookupval float = 33.33

declare @returnval int = (SELECT TOP 1 COLC FROM tablename 
							ORDER BY ABS( COLA - @lookupval ))

[Edit - apologies I misread this as requiring the closest value, not the closest still lower]

SQL

declare @returnval int = (SELECT TOP 1 COLC FROM tablename 
          WHERE cola < @lookupval ORDER BY COLA)

[Edit 2 - after OP comment]
Combining both my earlier approaches gives the closest value to the input that is lower than the input

SQL

declare @lookupval float = 33.33
declare @returnval int = (SELECT TOP 1 COLC FROM tablename 
	WHERE cola < @lookupval 
	ORDER BY ABS( COLA - @lookupval ))
print @returnval