By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,066 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,066 IT Pros & Developers. It's quick & easy.

Microsoft Excel - Compare two worksheets

P: 21
I didn't know where to post this question since it is an Excel question, but this was as close as I could get. The best way to ask my question is to give an example so...I have two worksheets in worksheet 1 I have column A with a length, column B with a width, and column C with a color. In worksheet 2 I have a stadard that lists all possible lengths in column A, all possible widths in row A, and all the corresponding colors are determined by matching a length and width. What I am needing is a way to compare my length and widths in worksheet 1 to the standard table in worksheet 2 so that I can check my colors. There can be another column in worksheet 1 that is populated with the found color or it can check the color I show in worksheet 1 and populate a True/False cell. I hope this makes sense....
Jul 29 '09 #1
Share this Question
Share on Google+
3 Replies

P: 122
First, let me see if I understand you correctly. Worksheet 1 looks something like this:

111 112 orange
456 789 blue

with the values in column A and B being some measure of length (inches, centimeters, etc). Then, worksheet 2 looks something like this:

111 111 green
111 112 orange
455 789 purple
456 789 blue

Is this correct? If so, then here's what you can do:

First, in worksheet 2, create a new column between B & C. In that column, enter a formula that combines the value in column A with the value in column B: (ex. =A1 & B1) That will build a string that you can compare your values in worksheet 1 against.

Now, in worksheet 1, enter the following formula in column D: =vlookup(A1 & B1, Sheet2!$C$1:$D$4, 2, FALSE) where the 4 parameters are:

1) The combined value in column A and column B,
2) the entire table on worksheet 2 that holds the combined values that we created and the color associated with those values,
3) the column from that table that you want to return - in this case the second column contains the colors so you enter 2, and
4) FALSE tells it to return a value only if it finds an exact match.

From here, you can create one more column on worksheet 1 that compares column C to column D (with the lookup values) and returns either TRUE or FALSE.

Hope this helps.
Jul 30 '09 #2

P: 21
Actually worksheet 2 looks like this

11 12 13 14
1 green blue red purple
2 black brown orange pink

Which is where I am having the problem. I have to be able to match say 12 and 2 to return the color brown. I don't know if I would be better off creating a new worksheet 2 and reorganizing the chart like your example, but there is a lot of data to input to make that happen, so I was hoping for an easier solution.

Thanks for the help!!
Jul 30 '09 #3

P: 122
Okay, so what you need to do is use the second column from worksheet one to define the offset for the vlookup. That's do-able.

First, in worksheet 2, insert a row right under the numeric values. In that row, enter the number 1 in column A, 2 in column B, 3 in column C, etc. That's what you'll use to determine the offset for the vlookup and you'll use an hlookup to do it. Now, worksheet 2 looks like this:

and worksheet 1 looks like this:

With this setup, you can use an hlookup formula to look up the value in the second column (11) and return the number directly below that value (2) in the lookup table (which is exactly the number you need for the third argument in the vlookup formula). And your vlookup formula can use the value from the first column in worksheet 1 (1) and the offset you just determined (2) to return the correct color (green).

Just make sure the lookup table you use in the hlookup formula is the area outlined in red and the table you use for the vlookup formula is the area outlined in blue.
Jul 30 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.