if,match,vlookup error

[jrobertson2403]

Hey there,

I am having trouble with this formula and am looking for some help with it. It works when vlookup finds the exact match, but when it doesn't, a N/A error code is displayed and I cannot seem to get rid of it. I am using this formula:

[/CODE]=IF(MATCH(A7,$C$1:$C$7,0),VLOOKUP(A7,$C$1:$D$7,2,0),B7)[CODE]

What I want it to do is if the value in column L is found in column B, then replace the value in column E with the value in column N. All of this is on the same row and the length of the columns change.

Any ideas?? I have attached an example sheet. Book1-example.xlsx

[Jonmo1]

Try

=IF(ISNUMBER(MATCH(A7,$C$1:$C$7,0)),VLOOKUP(A7,$C$1:$D$7,2,0),B7)

Or even better

=IFERROR(VLOOKUP(A7,$C$1:$D$7,2,0),B7)

[etaf]

I think you could use

=IFERROR (VLOOKUP(A7,$C$1:$D$7,2,0),B7)
so if vlookup cannot find the item , it will error and so not lookup
OR

=IF(ISERROR(MATCH(A7,$C$1:$C$7,0)), B7,VLOOKUP(A7,$C$1:$D$7,2,0))

[jrobertson2403]

Sorry...I put the wrong formula in. That was the formula that worked when I was using only columns A,B,C and D but still gave me the N/A code or would return a zero. Is there anyway to run the same formula for the way I described on the example sheet??

[jrobertson2403]

Could it be that my columns aren't located directly next to each other now?? Because I need to match column B and L together and pull the value from column O??

[etaf]

not sure where you are matching A7 with
sorry , not sure what you are trying to achieve - can you explain the spreadsheet in more detail

[etaf]

where do you want to put the value
and what do you want if they do not match

so in R put
=INDEX(O:O,MATCH(B3,L:L,0))

[jrobertson2403]

I would like it to do this:

=IF(MATCH(B3,$L$2:$L$7,0),VLOOKUP(L3,$N$2:$N$7,2,0),B3)

So if the value found in column L is found in column B, then replace the value of the cell in column E with the value found in column N. I am putting this formula in column E where I want the result to be placed. If there is no match, I do not want the cell to change.

Thank you for your patience. I really enjoy trying to figure these things out but I need sleep!! hahaha

[etaf]

you will need VBA to do that
you cant put a formula into a cell and keep the value thats already in the cell

[jrobertson2403]

Thank you very much for your help.

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.