PLEASE help me undestand this formula!
Posted by Rick on March 28, 2001 1:00 PM
=IF(A2<>"",VLOOKUP($A2,$J$1:$L$6,2,FALSE),"")
where $L$xx is the last cell of your data
THE Formula above will show data from ROW J.
=IF(A2<>"",VLOOKUP($A2,$J$1:$L$63,3,FALSE),"")
This formula will show data from Row K but for the
life of me I can't see or figure out where it specifies
the Row K? I need to know so I can effectively expand
this formula.
Thanks in Advance!
Posted by Mark W. on March 28, 2001 1:06 PM
Rick, the 3rd VLOOKUP argument is the nth lookup table
column from which a value will be returned. In
you 1st formula below A2 is compared against the
values in column J, and the corresponding value in
column K is returned because it's the 2nd column
in the range, $J$1:$L$6.
Posted by Rick on March 28, 2001 1:34 PM
Thanks Mark,
If I wanted to add a 4th column,(Column L) what
would it look like? I have tried and get nothing but
errors!!
Thanks in Advance!!!
Posted by Mark W. on March 28, 2001 2:06 PM
Column L is already a part of your lookup table --
it's the 3rd column. If you need to expand the
lookup table to include 4 column you'd use $J$1:$M$6
as your 2nd argument instead of $J$1:$L$6. I've
made this change to your VLOOKUP() below:
=IF(A2<>"",VLOOKUP($A2,$J$1:$M$6,2,FALSE),"")
Posted by Ian on March 28, 2001 2:17 PM
That ugly formula looks familiar! The vLook is composed of 3 arguments
1 = What you want to look up (Whats in A2)
2 = The range you want to look up from (j1..L6). To add another row you can make the J6..M6, etc. The first column of this range must contain the value you are looking up. Ie column J should have what matchs your value in cell A2. This list should always be sorted.
3 = What column number you want to return the value from. Ie enter a 1, the value from J is returned, 2 the value from K is returned etc.
So to add and look up a fourth value like sodium, put in the table in column M and your look up formula would be
=IF(A2<>"",VLOOKUP($A2,$J$1:$M$6,4,FALSE),"")
The if statement is just one of a few way to make it look good until your user enters something from the drop down list.
Good luck.
Posted by Aladin Akyurek on March 28, 2001 2:43 PM
Ian: Why does it look familiar to you? Just curious.
Aladin
Posted by Ian on March 28, 2001 2:46 PM
I suggested it to him a few days ago (12982.html)
