VLOOKUP issue

[rck]

Excel 2000

	A	B	C	D	E	F
1	RD_NO	OLD_DISTRICT	NEW_DISTRICT
2	LVA300	6	=VLOOKUP(A2,$E$2:$F$129,2,FALSE)=B2		LVB100	4
3	LVA301	6	=VLOOKUP(A3,$E$2:$F$129,2,FALSE)		LVB101	4
4	LVB100	5	=VLOOKUP(A4,$E$2:$F$129,2,FALSE)		LVB202	4
5	LVB101	5	=VLOOKUP(A5,$E$2:$F$129,2,FALSE)		LVB203	4
6	LVB200	6	=VLOOKUP(A6,$E$2:$F$129,2,FALSE)		LVB204	4
7	LVB201	6	=VLOOKUP(A7,$E$2:$F$129,2,FALSE)		LVB205	4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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