Re: VLOOKUP in sql

[Vinod Sugur]

Hi,

I converted the xls sheet into table having following structure:

CREATE TABLE `table_1` (
`No` decimal(10,0) unsigned NOT NULL,
`C` varchar(7) NOT NULL,
`D` varchar(7) NOT NULL,
`E` varchar(7) NOT NULL,
`F` varchar(7) NOT NULL,
`G` varchar(7) NOT NULL,
`H` varchar(7) NOT NULL,
PRIMARY KEY (`No`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I can get the result of following vlookup formula from the below queries:

a. VLOOKUP(869 ; "table" ; 3)
the end result is 4% (gets the value in the line below 870 (line: 770) and the column 3 of the table)

=======================
Select value
from
(select No, col,IF(col=1,C, IF(col=2,D, IF(col=3,E,IF(col=4,F,IF(col=5,G,IF(col=6,H,NULL)))))) value
from table_1, (Select 1 col
UNION
Select 2
UNION
Select 3
UNION
Select 4
UNION
SELECT 5
UNION
SELECT 6) 6_colunmns) vlookup

where no <= 869 /* first parameter of vlookup */
and col = 2 /* third parameter of vlookup -1 */
order by no desc limit 1
=======================

b. VLOOKUP(871 ; "table" ; 4)
the end result is 3% (gets the value in the line below 950 (line: 870) and the column 4 of the table)

=======================
Select value
from
(select No, col,IF(col=1,C, IF(col=2,D, IF(col=3,E,IF(col=4,F,IF(col=5,G,IF(col=6,H,NULL)))))) value
from table_1, (Select 1 col
UNION
Select 2
UNION
Select 3
UNION
Select 4
UNION
SELECT 5
UNION
SELECT 6) 6_colunmns) vlookup
where no <= 871 /* first parameter of vlookup */
and col = 3 /* third parameter of vlookup -1 */
order by no desc limit 1
=======================

Let me know if this works.

~Vinod