V and H lookup
Posted by Berney on December 06, 2001 2:14 AM
Ive been experimenting with nesting a Vlookup in an H lookup.
i have a table that looks a bit like this
1 2 3 4 5
red 2 a b c d e
green 3 f g h i j
black 4 k l m n o
blue 4 p q r s t
the formula i am using is this:
=HLOOKUP(B1,E2:I5,(VLOOKUP(A1,C3:D5,2)))
b1 is a row eg. 1, 2,
e2:i5 is the table reference
A1 is a colour.
so it should [hopefully] look along the table for the number, then when deciding which row to display, search for the colour.
Unfortunately this doesn't work.
please help!
Posted by Hansoh on December 06, 2001 12:38 PM
without looking at your code in detail, i THINK the problem may be that one of the constraints of LOOKUP funcations is that the arrays in LOOKUP functions must be in alphabetical order (whether it's by rows or columns).
i'm not sure your data is in order. also, i'm not sure what you mean by 'doesn't work'. do you mean that the function bombs or do you mean that it returns the wrong/unexpected answer? please clarify. see if that helps.
han
