excel formula newbie need help 2

[g4j4hgil4]

hi,
i know this might be an stupid question but, here goes..

for example i have this
A B C D
123 a 789
456 b 222
789 c 123
432 d 456

here's what i want to accomplised
A B C D
123 a 789 c
456 b 222 d
789 c 123 a
432 d 456 0

i need to compare value in cell A with every cell in C column, if match then cell d value is the cell b(column) value
if nothing match then 0

please could somebody enlight me,
i'm new to this formula thingie

 

[110877]

Here is the formula:
=IF(C4=A4,"a",IF(C4=A5,"b",IF(C4=A6,"c",IF(C4=A7,"d",0))))

Let me know if this doesn't work.
Unikorn. (110877)

 

[g4j4hgil4]

hi,
thanx for your reply
the formula is not working, but from your formula i think i have confuse you with my question (sorry for my bad english)

i want A1 value to be check n compare with every cell in C
if A1 value equal value in C3(for exmpl) then D1 value shoud be c(thats value taken from B3)
and so on to A2,A3,A4...

i could do it manually but i got a huge list here
like 200 rows, and the value is not that simple either

thanks again

 

[GlennUK]

This is the formula you need ...

=IF(ISNA(MATCH(A1,$C$1:$C$200,0)),0,INDEX($B$1:$B$200,MATCH(A1,$C$1:$C$200,0)))

enter this in D1, and copy down for 200 rows.

Glenn.

 

[joerd]

why not change the order of columns B and C, and then use, in cell D1
=VLOOKUP(A1,$B$1:$C$200,2,false)
and copy this formula down to D2200 Regards,

Joerd

 

[Zathras]

I think your example is slightly off, the value next to 222 should be 0 and the value next to 456 should be "b"

If that assesment is correct, and if the first entry is in cell A1, then put this formula in D1 and copy it down as far as needed. (Adjust the range definition to suit. Actually, it would be best to assign a range name to A1:B4 (in this example) and use that instead of "hard-coded" cell addresses.

=IF(ISNA(VLOOKUP(C1,$A$1:$B$4,2,0)),0,VLOOKUP(C1,$A$1:$B$4,2,0))

If you assign a range name "SearchMe" to the appropriate 2-column range (A1:B4 in this example), the formula looks like this:

=IF(ISNA(VLOOKUP(C1,SearchMe,2,0)),0,VLOOKUP(C1,SearchMe,2,0))

Using range name instead of cell addresses makes it easier to handle when new items are added to the spreadsheet.

 

[g4j4hgil4]

thanks so much guys

GlennUK, i try your formula n its working great just what i need thanks a lot

joerd, yours are right and simple, now i need to convert all that "#N/A" to 0

zatrhas, my example are correct and they've been solved
thanks