Vlookup with two right functions

Hi all, I'm a little stuck with a problem.

I am trying to reference the last 5 characters (numerical) from a range of cells (L2:L5) in worksheet "Report", and then using vlookup i want to look in worksheet "Data". In "Data" I want to lookup the last 5 characters from the column range $A$2:$A$5 and give the result from column C in "Data" back into worksheet "Report".

=VLOOKUP(ISNA(RIGHT(L2,5),'[Data.xlsx]Data'!$A$5:$C$5,3,FALSE),"No Match",L2)

I have tried this formula, but I know it isn't quite there, and I haven't been able to figure out the correct way to use the right function twice. I am fairly new to excel btw, so I'm probably trying to run before I can walk lol!

I would also like to report "No match" if there is no matching criteria, which I believe involves ISNA.

Thanks in advance :-)

Jeppers,

try this. updated

=IF(ISNA(VLOOKUP("*"&RIGHT(L2,5),'[Data.xlsx]Data'$A$5:$C$5,3,0)),"No Match",VLOOKUP("*"&RIGHT(L2,5),'[Data.xlsx]Data'$A$5:$C$5,3,0))

Check the range of DATA tab. It is using a single row for the vlookup.

and also what is L1 doing there.

What i mean is you have vlookup wherein you will return the value from column 3, right?
then if you cannot find any match for your vlookup it will return "No Match"
so what will be the use of returning the value of L2, on what instances?

self withdraw this part....

Follow post #6 for the other guides

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

lookup_value --this is the formula-> right(L2,5)
table_array, col_index_num -- this is hte range on where the lookup will search the WHOLE DATA -> A2:C4
[range_lookup] - this is tyhe column number where you want to pull the value so column C which is -> 3

=VLOOKUP("*"&RIGHT(L2,5),'[Data.xlsx]Data'$A$2:$C$4,3,0))

now in case of error you can use iferror as stated in post #6

IFERROR(VLOOKUP(...),"type here whatever you want") --->>>you can use this for error handling

iferror(VLOOKUP("*"&RIGHT(L2,5),'[Data.xlsx]Data'$A$2:$C$4,3,0)),"NO MATCH")

You are very much welcome from all of us.
You can mark this thread "SOLVED"

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.

Regards,
Vlady

You are very much welcome from all of us.
You can mark this thread "SOLVED"

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.

Regards,
Vlady