If match select value three cells accross

[kurifodo]

Hi everyone!

I am trying to get a match a value between two cells in different sheets, if it matches to then copy the value three cells accross of the value used to make the comparison.
Eg:
Sheet 1
A1 B1 C1
Name Date Price

Sheet 2
A1 B1 C1
Name Something Price(to be copied!)

So if the names match to copy the value to be able to do some calculations on it.

The formula I've trying to get to work is:
=IF(ISERROR(MATCH(A1, Sheet1!A1:A100,0),"(WHAT TO PUT HERE TO GO ACCROSS?)","")


Any help would be much appreciated!

Kurifodo

[Pete_UK]

Try this:

=IF(ISNA(MATCH(A1, Sheet1!A1:A100,0)),"",VLOOKUP(a1:Sheet1!$A$1:$C$100,3,0))

Hope this helps.

Pete.

[kurifodo]

It gave me a value error ... Thsi means the equation is right just the contects wrong ? :S
Thanks for the speedy help so far!

[kurifodo]

And if I change it a bit to reference error :S Not sure what I've done wrong?

=If(ISNA(Match(B20,Sheet1!A2:A31,0)),"",Vlookup(B20,Sheet1!A2:A31,3,0))

[Pete_UK]

Post an example workbook so that we can see exactly what you are trying to do - the FAQ describes how to do this if you are unsure.

Pete

[Pepe Le Mokko]

There was a small typo in Pete's post #2

Please Login or Register  to view this content.

Also possible is

Please Login or Register  to view this content.

=If(ISNA(Match(B20,Sheet1!A2:A31,0)),"",Vlookup(B20,Sheet1!A2:A31,3,0))

won't work, you have a one column range in the Vlookup and are searching for a result in the3rd column..