Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 2 excel documents with a lot of data (near 20.000 columns) and I have to do the following:

Col 1 - Col 2 - Col 3 - Col 4

A - 1 - B - 2
B - 2 - F - 
C - 3 - G - 
D - 4 - H - 
E - 5 - A - 1
.....
.....

In the example above, in the cell next to B (column 3) should print 2 and in the cell next to A should print 1 SO i have to scan columns 1 and 3 for matching, then when cells match print in column 4 whats in column 2

I should note that all cells are text

share|improve this question

Assuming that Col 1 is column A, etc, and the data starts in row 2, put this formula in cell D2 and copy down.

=iferror(vlookup(C2,$A$1:$B$50000,2,false),"")

IFERROR() exists in Excel 2007 and later. For earlier versions use

=if(iserror(match(C2,$A$1:$A$50000,0)),"",vlookup(C2,$A$1:$B$50000,2,false))
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .