find and replace data

R

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 |
 
N

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.
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top