VLOOKUP issue

[rck]

Excel 2000

	A	B	C	D	E	F
RD_NO
LVA300	LVB100	4
LVA301	LVB101	4
LVB100	LVB202	4
LVB101	LVB203	4
LVB200	LVB204	4
LVB201	LVB205	4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]OLD_DISTRICT[/TD]
[TD="align: center"]NEW_DISTRICT[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]=VLOOKUP(A2,$E$2:$F$129,2,FALSE)=B2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]=VLOOKUP(A3,$E$2:$F$129,2,FALSE)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]=VLOOKUP(A4,$E$2:$F$129,2,FALSE)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]=VLOOKUP(A5,$E$2:$F$129,2,FALSE)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]=VLOOKUP(A6,$E$2:$F$129,2,FALSE)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]=VLOOKUP(A7,$E$2:$F$129,2,FALSE)[/TD]
[TD="align: right"][/TD]

</tbody>

All RDs​

Excel 2000
	A	B	C	D	E	F
1	RD_NO	OLD_DISTRICT	NEW_DISTRICT
2	LVA300	6	#N/A		LVB100	4
3	LVA301	6	#N/A		LVB101	4
4	LVB100	5	4		LVB202	4
5	LVB101	5	4		LVB203	4
6	LVB200	6	#N/A		LVB204	4
7	LVB201	6	#N/A		LVB205	4
All RDs

I would like to keep the value in B2 if it does not change based of the VLOOKUP formula. IF A2 does not match E2, then keep the existing value in B2. If it does match, then change the value in B2 to the value in F2.

The VLOOKUP formula in C3 works for changing the value, but inserts #N/A if it doesn't find a match.

Thanks in advance!

 

[jamtay317]

=<code>if(isna(vlookup(</code>[COLOR=#333333][FONT=Verdana][/FONT][/COLOR][CENTER][COLOR=#333333][FONT=Verdana]A2,$E$2:$F$129,2,FALSE)),b2,[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]A2,$E$2:$F$129,2,FALSE))[/FONT][/COLOR][/CENTER]

try this <code>

</code>

 

[Andrew Poulsom]

Maybe try:

=IF(COUNTIF($E$2:$E$129,A2),IF(VLOOKUP(A2,$E$2:$F$129,2,FALSE)=B2,B2,VLOOKUP(A2,$E$2:$F$129,2,FALSE)),B2)

 

[jamtay317]

=<code>if(isna(</code>VLOOKUP(A2,$E$2:$F$129,2,FALSE)),b2,VLOOKUP(A2,$E$2:$F$129,2,FALSE))

 

[Rick Rothstein]

Does this formula do what you want?

=IFERROR(INDEX(F$2:F$129,MATCH(A2,E$2:E$129,0)),B2)

 

[rck]

That was it, Andrew! Thank you so much!

Maybe try:

=IF(COUNTIF($E$2:$E$129,A2),IF(VLOOKUP(A2,$E$2:$F$129,2,FALSE)=B2,B2,VLOOKUP(A2,$E$2:$F$129,2,FALSE)),B2)

 

[markmzz]

Maybe this:

=IF(ISERROR(1/(VLOOKUP(A2,$E$2:$F$129,2,0)<>B2)),B2,VLOOKUP(A2,$E$2:$F$129,2,0))

Markmzz

 

[Rick Rothstein]

Does this formula do what you want?

=IFERROR(INDEX(F$2:F$129,MATCH(A2,E$2:E$129,0)),B2)

I just realized your are using XL2000. I do not have that version available, so I cannot test these, but I think one of these should should work for you. Try this one first...

=IF(COUNTIF(E$2:E$129,A2),INDEX(F$2:F$129,MATCH(A2,E$2:E$129,0)),B2)

and if it doesn't work, then try this one...

=IF(ISERROR(MATCH(A2,E$2:E$129,0)),B2,INDEX(F$2:F$129,MATCH(A2,E$2:E$129,0)))

 

[markmzz]

A small modification in the formula:

=IF(ISERROR(VLOOKUP(A2,$E$2:$F$129,2,0)),B2,VLOOKUP(A2,$E$2:$F$129,2,0))

Markmzz