Vlookup using awk

Hi folks,

awk 'NR==FNR {m[$1]=$0; next} $1 in m{$0=m[$1]} {print}' file2 file1

Works a charm for a vlookup type query, sourced from http://www.unix.com/shell-programming-and-scripting/215998-vlookup-using-awk.html

However my column content has white spaces and numbers. Example

file1

The Man on the moon 1
The Man on Jupiter 2
The Man on Mars 3
The Man on Earth 4

file2

The Man on the moon 1        https://www.example1.com/
The Man on Jupiter 2        https://www.example2.com/
The Man on Mars 3       https://www.example3.com/

awk 'NR==FNR {m[$1]=$0; next} $1 in m{$0=m[$1]} {print}' file2 file1

returns

The Man on Mars 3        https://www.example3.com/
The Man on Mars 3        https://www.example3.com/
The Man on Mars 3       https://www.example3.com/
The Man on Mars 3        https://www.example3.com/
<empty line>

Files are \t delimited. How can I get awk to give me my desired output

The Man on the moon 1        https://www.example1.com/
The Man on Jupiter 2        https://www.example2.com/
The Man on Mars 3       https://www.example3.com/
The Man on Earth 4       Not Available

Any help would be greatly appreciated.

If you input file: file2 is indeed tab separated, then you can try:-

awk -F'\t' '
        NR == FNR {
                m[$1] = $0
                next
        }
        {
                if ( $1 in m )
                        $0 = m[$1]
                else
                        $0 = $1 OFS "Not Available"
                print
        }
' OFS='\t' file2 file1

Hello pshields1984,

Could you please try following and let me know if this helps you.

awk 'FNR==NR{Q=$NF;$NF="";gsub(/[[:space:]]+$/,X,$0);A[$0]=Q;next} {Q=($0 in A)?$0 OFS A[$0]:$0 OFS "NA";print Q;Q=""}' file2 file1

Thanks,
R. Singh

That works perfectly Yoda, many thanks. How can I turn your example into a one-liner? Is that possible? I've tried

awk -F'\t' 'NR == FNR {m[$1] = $0; next} {if ( $1 in m )$0 = m[$1] else $0 = $1 OFS "Not Available"}' OFS='\t' file2 file1

but I'm getting

awk: cmd. line:1: NR == FNR {m[$1] = $0; next} {if ( $1 in m )$0 = m[$1] else $0 = $1 OFS "Not Available"}
awk: cmd. line:1:                                                        ^ syntax error

many thanks

---------- Post updated at 10:44 AM ---------- Previous update was at 10:42 AM ----------

This worked brilliantly thank you.

Try also

awk -F"\t" '
NR == FNR       {m[$1] = $NF
                 next
                }
!($1 in m)      {m[$1] = "Not Available"
                }
                {$(NF+1) =  m[$1]
                }
1
' OFS='\t' file2 file1

You missed few semicolons:-

awk -F'\t' 'NR==FNR{m[$1]=$0;next}{if($1 in m) $0=m[$1];else $0=$1 OFS "Not Available";print}' OFS='\t' file2 file1

Thanks Yoda, it works perfectly for me.