Problem with vlookup and isna function

[marko]

I have following problem:

........ A........B........ C........ D........ E

1.......A.......55................. A........23
2.......B.......54................. B........45
3.......C.......76................. D........32
4.......D.......58................. F........55
5.......E.......34................. I.........64
6.......F.......54................. J.........56
7.......G.......89
8.......H.......10
9.......I........56
10.....J........34

If the expression from column A (example A1=A) match the sam
expression in column D (A1=D1, both are A), then the nuber in column
must appear in cell C1 (C1=23). In column D are no all expressions a
in column A.

I starded to solve this problem with function in column C, C1:
=IF(ISNA(VLOOKUP(A1;$D$1:$D$10;1;FALSE));"No";"Yes")

This function checks if the expressions in column A are also in colum
D, if are, then as an example in cell C1 aperars "yes", and in cell C
"no".
Instead of this yes and no I want to apperar the numbers from column
in colum C, but in the same row (in the row where are expressins fro
column A) and where the expression from columns A and D are matchin
(in cell C10 must appear the nuber from cell E6 (56) becaus
A10=D6=(J).

Thanks,
Mark

 

[Frank Kabel]

Hi
you're nearly there. use the following in C1:
=IF(ISNA(VLOOKUP(A1;$D$1:$E$10;2;FALSE));"No";VLOOKUP(A1;$D$1:$E$10;2;F
ALSE))
and copy down

 

[Bob Phillips]

Hi Marko,

Try this

=IF(ISNA(MATCH(A1,D,0)),"",INDEX(E:E,MATCH(A1,D,0),1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[marko]

thanks all.
you´ve been most helpfull.
mark