Tell me more ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I would like to do a vlookup in unix or gawk. I have a file with a column of numbers (file1) I have a file with several columns (file2) I want to look up for the numbers I have in file 1 into column one of file 2

I was using grep -f file1 file2 but this prints all the lines in file two containing a string as in file 1, not just the lines that have the string in column one of file 2

How can I solve this with gawk or other unix tools?

share|improve this question
What's a vlook up? – Gilles Aug 7 at 23:02

1 Answer

while read vl ; do grep "^$vl " file2 ; done < file1

read looks for input terminated by a newline and places in the variable vl. By wrapping this in a while loop this is repeated for all contents of STDIN. By using < file1 we redirect the contents of file1 into STDIN, so the loop repeats for all contents of file1. For each line of file1 (stored in vl) we grep in file2 with the search term prefixed by ^. This ties the search term to be at the beginning of the line in file2. I've also added a space at the end of the grep search string (I'm assuming file2's column separator is a space), so that it will only match exactly and not sub matches (if the search term is foo and file2 has a row with foobar as column one it will not get matched). Replace that space with whatever your field separator of file2 is if it is not space.

If you can change file1 at will then:

sed -i 's/^\(.*\)$/^\1 /' file1

and then your original grep -f will work (note again I've used space as a separator - after the \1).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.