S
Salman
A B C D
A 3 a 3
B 4.5 a 10.5
C 6 a 15
D 7.5 b
A 9 b
B 10.5 b
a 12 b
E 13.5 c
D 15 b
A 16.5 e
I NEED VALUES IN COLUMN 'D' FOR LOOKUP VALUE IN COLUMN 'C' ORIGINAL DATA IS
IN COLUMN 'A' AND 'B'. I HAVE ENTERED THE FORMULA AS BELOW: ENTERED AS AN
ARRAY CTRL+SHIFT+ENTER
=IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=C1,ROW($A$1:$A$10)),ROW(1:1)),2)),"",INDEX($A1:$B$10,SMALL(IF($A$1:$A$10=C1,ROW($A$1:$A$10)),ROW(1:1)),2))
AS I DRAG DOWN THE FORMULA ROW(1:1) CHANGE AND AT FOURTH PLACE FOR VALUE "B"
IT BECOMES ROW(4:4), IS THIS THE PROBLEM, IF IT IS, THAN IS THERE ANY
SOLUTION ?
ABOVE FORMULA RETURNS THE VALUE ONLY FOR "A" NOT FOR VALUE B - C - & D.
ANY SUGGESTION SO I COULD GET VALUE FOR ALL THE LOOKUPS VALUES.
THIS IS ONLY AN EXAMPLE ORIGINAL DATA IS MORE COMPLEX AND ON DIFFERENT SHEETS.
ANY HELP APPRECIATED.
REGARDS
ASAD
A 3 a 3
B 4.5 a 10.5
C 6 a 15
D 7.5 b
A 9 b
B 10.5 b
a 12 b
E 13.5 c
D 15 b
A 16.5 e
I NEED VALUES IN COLUMN 'D' FOR LOOKUP VALUE IN COLUMN 'C' ORIGINAL DATA IS
IN COLUMN 'A' AND 'B'. I HAVE ENTERED THE FORMULA AS BELOW: ENTERED AS AN
ARRAY CTRL+SHIFT+ENTER
=IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=C1,ROW($A$1:$A$10)),ROW(1:1)),2)),"",INDEX($A1:$B$10,SMALL(IF($A$1:$A$10=C1,ROW($A$1:$A$10)),ROW(1:1)),2))
AS I DRAG DOWN THE FORMULA ROW(1:1) CHANGE AND AT FOURTH PLACE FOR VALUE "B"
IT BECOMES ROW(4:4), IS THIS THE PROBLEM, IF IT IS, THAN IS THERE ANY
SOLUTION ?
ABOVE FORMULA RETURNS THE VALUE ONLY FOR "A" NOT FOR VALUE B - C - & D.
ANY SUGGESTION SO I COULD GET VALUE FOR ALL THE LOOKUPS VALUES.
THIS IS ONLY AN EXAMPLE ORIGINAL DATA IS MORE COMPLEX AND ON DIFFERENT SHEETS.
ANY HELP APPRECIATED.
REGARDS
ASAD