VLOOKUP Getting rid of #NA

[1kmonro]

I'm using VLOOKUP to grab data and fill fields in a column. There are two conditions I want to prevent.

The first is when the lookup doesn't find a match. It currently returns a #NA. I want it to just leave the field alone.

The second is when it finds a match but there is already data in the field - again I want it to leave the field alone.

Is there a way to do this?

 

[hiker95]

1kmonro,

Please post the VLOOKUP formula that is returning "#N/A".

What do you want to return in the cell if there is an error? Blank or zero?

 

[Sneech#7]

What do you mean by leave the field alone? You can enclose the vlookup in an ISNA function: for example =if(isna(vlookup(...)),"",vlookup(...))

 

[1kmonro]

Here's the formula:

=VLOOKUP(C94,'PaymentInfo'A4:F428,6,FALSE)

It grabs the parent code (C94) and looks for a match in the PaymentInfo sheet. This sheet is overwritten at least 3-7 times a month so sometimes a particular parent code is there and sometimes it isn't. When it finds a match it moves to column 6 and pulls the data back into the original sheet. If there's no match it goes back to the original sheet and enters an #NA.

I would truly like it leave the target field blank if there is no match. Or (and I didn't mention this originally) I would like it to sum what is currently in the target field with the new amount that it found.

I hope that this makes sense but want to thank you for even responding.

 

[Sneech#7]

In that case, using the IF and ISNA should work

 

[hiker95]

1kmonro,

Try:

=IF(ISNA(VLOOKUP(C94,PaymentInfo!A4:F428,6,FALSE)),"",VLOOKUP(C94,PaymentInfo!A4:F428,6,FALSE))

 

[Runes]

Sorry to butt in on 1kmonro's question but I have one relating to it. Can this formula be used as a conditional format for an entire sheet or can you suggest one that might work?