Using Wildcards in Find & Replace

[uhcord98]

I'm doing a search simular to this

=VLOOKUP(D*,Sheet2!A*:B*,2,FALSE)

and I want it replaced with this

=VLOOKUP(D3,Sheet2!A1:B42,2,FALSE)

HOWEVER when I do the find & replace I don't want it to change whatever the current value is to D3 I want it to stay as whatever it was already....so how do I exempt the "D" value from the search??

[Bryan Hessey]

I'm doing a search simular to this

=VLOOKUP(D*,Sheet2!A*:B*,2,FALSE)

and I want it replaced with this

=VLOOKUP(D3,Sheet2!A1:B42,2,FALSE)

HOWEVER when I do the find & replace I don't want it to change whatever the current value is to D3 I want it to stay as whatever it was already....so how do I exempt the "D" value from the search??

Hi,

is D required, or can you succeed with Replace ,Sheet2!A*:B*,2,FALSE) with ,Sheet2!A1:B42,2,FALSE)

---

[starguy]

I'm doing a search simular to this

=VLOOKUP(D*,Sheet2!A*:B*,2,FALSE)

and I want it replaced with this

=VLOOKUP(D3,Sheet2!A1:B42,2,FALSE)

HOWEVER when I do the find & replace I don't want it to change whatever the current value is to D3 I want it to stay as whatever it was already....so how do I exempt the "D" value from the search??

Hi
Does this work

=VLOOKUP(D?,Sheet2!A?:B?,2,FALSE)

[uhcord98]

"D" is required for the formula to work, however I don't want ANY of that part of the formula to be replaced when I do the find command because the D values are in consecutive order, D1, D2, D3, because those are the cell that is being looked up and the A1:B42 will always be the same because it's the range table excel is looking in to find the result.

[Bryan Hessey]

"D" is required for the formula to work, however I don't want ANY of that part of the formula to be replaced when I do the find command because the D values are in consecutive order, D1, D2, D3, because those are the cell that is being looked up and the A1:B42 will always be the same because it's the range table excel is looking in to find the result.

That was not my question.

Can you succeed with Replace ,Sheet2!A*:B*,2,FALSE) with ,Sheet2!A1:B42,2,FALSE)
or are there similar formula working on a column that is not D?

---

[uhcord98]

That was not my question.

Can you succeed with Replace ,Sheet2!A*:B*,2,FALSE) with ,Sheet2!A1:B42,2,FALSE)
or are there similar formula working on a column that is not D?

---

Worked like a charm, you my friend kick a$$.

[uhcord98]

First off, let me thank all of you guys for your help. You rock.

Now I'm going to contine to be a pest and ask another find & replace question.

=IF(H*=I*,E*,0)

Same scenario...the only value I want to utilize in the find & replace is this "I".

I tried =I* and replacing with =I1..and it replaced every IF formula with I1 lol

[daddylonglegs]

Perhaps you can do some sort of 3 way replace.

First replace IF with something that won't appear anywhere in your formulas, e.g. "zzz".

Then replace your I* as before

Then replace "zzz" with IF......

[uhcord98]

I replaced all of the IF with ZZZ resulting in these type of formulas

=zzz(H2=I1,E2,0)
=zzz(H3=I2,E3,0)
=zzz(H4=I3,E4,0)

if I then do a find replace using I* and replacing with I1 it gives me this:

=zzz(H2=I1)
=zzz(H3=I1)
=zzz(H4=I1)

it took out the E value and the 0. Not quite sure if I did it right though.

[daddylonglegs]

Try replacing

I*,E

with

I1,E

[uhcord98]

It keeps spitting an error back at me saying "this formula contain an error"

I attached the book I'm working with to see if you guys have any better luck than I do.

[daddylonglegs]

OK, I think you're adopting the wrong approach here. If you want the I1 to remain the same but the H2 and E2 to increment then you should use a $ to make the reference "absolute". I.e. make the formula in I2

=IF(H2=I$1,E2,0)

the $ before the 1 means that the 1 doesn't change when you copy the formula down.

Put the cursor on the bottom right corner of I2 until you see a black + then double-click and the formula should be copied down. You could adopt the same approach with your VLOOKUP. Use

=VLOOKUP(D2,Sheet2!A$1:C$999,2,FALSE)

and copy down

[uhcord98]

OK, I think you're adopting the wrong approach here. If you want the I1 to remain the same but the H2 and E2 to increment then you should use a $ to make the reference "absolute". I.e. make the formula in I2

=IF(H2=I$1,E2,0)

the $ before the 1 means that the 1 doesn't change when you copy the formula down.

Put the cursor on the bottom right corner of I2 until you see a black + then double-click and the formula should be copied down. You could adopt the same approach with your VLOOKUP. Use

=VLOOKUP(D2,Sheet2!A$1:C$999,2,FALSE)

and copy down

That works like a charm! One final question on this....how would I use the ISERROR command in that formula?

[daddylonglegs]

....how would I use the ISERROR command in that formula?

Which one? You could return a blank rather than #N/A from the VLOOKUP by using this formula in H2 copied down

=IF(ISNA(MATCH(D2,Sheet2!A$1:A$999,0)),"",VLOOKUP(D2,Sheet2!A$1:B$999,2,0))