find and replace data

[Rob]

How would I find and replace data conditionally. In the example below I
would like to take Workbook 2, A1:A3 and replace abc with 123. Something
like:
=IF(A1:A3=abc,substitue [123],"") but that doesn't work.

Workbook 1
| A | B | C |
1|abc | 123 | |
2|bcd | 234 | |
3|cde | 345 | |
Workbook 2
| A | B | C |
1|abc | XX | XX |
2|bcd | XX | XX |
3|cde | XX | XX |

 

[NoodNutt]

G'day Rob

You could try something like:

Either in WorkBook1 (somewhere off screen), or in a NewWorkSheet, you could
setup an Array.

Assumed NewWorkBook
A B
ABC 123
BCD 234
CDE 345
DEF 456
EFG 567
FGH 678
GHI 789

In WorkBook1

B1 =

=IF('WorkBook2'$A1="","",(LOOKUP(WorkBook2!$A1,NewWorkBook!$A$1:$B$7,NewWorkBook!$B$1:$B$7)))

Copy Down as required

HTH
Mark.

 

[Rob]

By messing around I stumbled on the vlookup function which does exactly what
I was looking for. I built an array on a seperate workbook and referenced it
in the vlookup function. Works like a charm.

 

[NoodNutt]

Glad I could help Rob

Appreciate the feedback.

Mark.