using Excel lookup equivalent function in SQL

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

using Excel lookup equivalent function in SQL

Author

Topic

narlyone
Starting Member

2 Posts

Posted - 2011-08-31 : 08:28:18

Hi there.

First up, I'm an absolute noob at SQL and have little clue as to what I'm doing!

I'm trying to replicate the following Excel lookup array so that our sql database gives me the same result (without having to nest a Replace function multiple times) This is the Excel array:

=LOOKUP(E2,{0,15,18,27,37,46,57,68,79,89,94},{"N","2","3c","3b","3a","4c","4b","4a","5c","5b","5a"})

This is what I have currently for SQL:

Replace(Satsfilter('KS2','MAT','TT','SUB','NS',Student.Satresults.Result),'','0','N','15','2','18','3c','27','3b','37','3a','46','4c','57','4b','68','4a','79','5c','89','5b','94','5a')

Note that I have script SQL so that it's in a string (for use of a better term).

With the Excel lookup array, if a number in the table is between 46 and 57, the value displayed is 4c

My query is how to do the same using SQL?

Any help greatly received

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 08:48:27

you need to implement it using mapping table in sql. Mapping table will be table with two fields one storing your first value array and other one with second value array. then instead of lookup you use join in sql like

INNER JOIN MappingTable t
ON t.Field1 = <your field containing E2 values in table>

here Field1 contains first array values {0,15,18,27,37,46,57,68,79,89,94}
and in SELECT you will return t.Field2 which will be value from your second array {"N","2","3c","3b","3a","4c","4b","4a","5c","5b","5a"}

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

narlyone
Starting Member

2 Posts

Posted - 2011-08-31 : 11:09:03

Ay Caramba! Thanks for your reply

I'll get the database support team to sort it out for me instead - I was hoping for a quicker way using script but there's no way I'm lifting the hood of this database

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 12:22:17

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources