CODEWITHSUNDEEP
excelspreadsheet
Xoax
Xoax

Reputation: 77

How to pair 2 columns together in Excel?

For example, One table is the formula, and the other is the data sheet.

For the formula, I want Column A to pair to Column B. So, ZIPCODE1 in A1 equates to $6 in B1. Like

12345 - $5
54312 - $4
12422 - $7

So, in the data sheet, whenever I type a zipcode, it will autofill/calculate with that dollar amount.

Upvotes: 0

Views: 5429

Answers (1)

teylyn
teylyn

Reputation: 35915

Sounds like you may be after a lookup formula.

You need to have a table like the above in your workbook, for reference. Let's say that this table is in Sheet2, from cell A1 to B4. Make row 1 the labels, ZipCode and Fee, so everybody can easily recognize what data the columns hold.

Let's further assume that in Sheet1 of your workbook you have a zip code in cell A1 and you want to see the respective price in cell B1. You now can use this formula in cell B1

=vlookup(A1,Sheet2!$A$1:$B$100,2,False)

In words: find the value from A1 (of the current sheet) in the first column of the table in Sheet2 that starts in A1 and goes to B100 (I'm using row 100 as an arbitrary last row of the table. There are more advanced ways to make the table reference dynamic). Return the value in the second column where column A has an exact match. For more info use the Excel help for Vlookup.

Another option is an Index/Match combo

=Index(Sheet2:$B:$B,match(A1,Sheet2!$A:$A,0))

In words: look at column B in sheet 2 and return the cell where column A in Sheet2 has an exact match for my value in A1 (in the current sheet).

Upvotes: 2

Related Questions