If Statements and Vlookup - compare to sheets

Microsoft Excel 2003 (full product)
September 22, 2010 at 07:53:24
Specs: Windows XP
I have a workbook with two sheets Sheet 1 called Raw Data from IW and Sheet 2 called Raw Data to ICI

Sheet 1 containts a column of numbers like 2548-1234-1531-612 and a column of single digit numbers next to it like 1 or 2. Column 1 is a list of document numbers and column 2 are their related revision.

Sheet 2 contains the same type of information. I need Sheet 1 to search Sheet 2 and tell me if a number appearing in Sheet 1 also appears in Sheet 2, if yes then return revision from column 2 in Sheet 2 and display in Column 3 on Sheet 1.

Can anyone help with this? I've been trying nested if's and vlookup and not having any luck.

thank you.

See More: If Statements and Vlookup - compare to sheets

Report •

September 22, 2010 at 08:16:00
Are the document numbers unique within each list or are there duplicates in either Sheet1 Column A or Sheet2 Column A?

If they are unique, then a simple VLOOKUP should work:

In Sheet1!C1 enter =VLOOKUP(A1, 'Raw Data to ICI'!$A$1:$B$10, 2, 0)

Report •

September 22, 2010 at 08:25:50
There will be duplicate drawing numbers on Sheet 2 but duplicate revs

Sheet could have:

1552-8999 1
1552-8999 0
1552-8999 2

Report •

September 22, 2010 at 10:43:22
re: "There will be duplicate drawing numbers on Sheet 2 but duplicate revs

Do you mean duplicate numbers but different revs?

If so, your initial request no longer makes sense.

If you want to return the Rev number from Sheet 2 to Sheet1 Column C, but you have multiple Rev numbers on Sheet 2, which Rev number do you want returned?

Report •

Related Solutions

September 22, 2010 at 11:13:21
I'm sorry my bad typing got in the way on that one.

There will be duplicate drawing numbers, but not duplicate revisions.

Like such:

Column A Column B
2511-89 1
2511-89 0
2511-89 4

So Sheet 1 colum A, searches Sheet 2 column A for a match, if it finds a match then it returns the corresponding revision from Column B Sheet 2, to Column C Sheet 1. The problem is not only getting the formula to do that but to also have it display only the highest revision. So if on Sheet 2 I have 4 duplicate drawing numbers and 4 unique rev's to match them, i only want the search to return the highest revision, ie return result rev 2 instead of rev 1 or rev 0

Does that help?

Report •

September 22, 2010 at 12:25:53
re: "display only the highest revision."

hmm...do you think perhaps that that was an important piece of information to share with us, like maybe in your original post?

Sort Sheet2 in descending order based on Column B. That will put the highest rev level as the first number next to each respective drawing number.

Now use a standard VLOOKUP.

Since VLOOKUP stops when it finds the first occurrence of the lookup_value in the lookup_array, it will find return the highest rev level kind of "on accident".

Report •

September 22, 2010 at 12:29:45
LOL it would have been if I had known - My peeps like to make sudden changes and additions when you least expect it.

I definitly appreciate the help you've given me :)

I've only used vlookup once or twice before so i have to ask will my formula look like the one you used above?
=VLOOKUP(A1, 'Raw Data to ICI'!$A$1:$B$10, 2, 0)

thank you for having patience :)

Report •

September 22, 2010 at 12:51:39
It should, assuming your lookup_array is in A1:B10. Adjust as required.

If you plan to use VLOOKUP (or any Excel function) you should look it up in the Excel Help files or Google it.

What values you use for the various arguments impact how it works, so you really should understand what each of them mean.

Report •

September 22, 2010 at 13:20:45
Thank you so much :) You've been wonderful.

I have one more question for you.

Can I make a formula that will search Column B and Column C, then Highlight the corresponding cell in Column A if the cell in Column B was at a higher number than the one in Column C.

Such as:

Column B Column C
2 1

In this case the corresponding cell in Column A would be be highlighed because the value in Column B is higher than the one in Column C.

But if C had been higher than B then no highlight would have taken place.

Thank you for bearing with me, i have very little excel experience, but i'm trying my best to learn on the go :)

Report •

September 22, 2010 at 13:37:37
Technically, I shouldn't answer this question in this thread since we prefer that each question be in it's own thread so that it can be found in the archives.

In this case, I'll bend the rules since I assume you are trying to highlight the drawing numbers that have the higher rev number on Sheet1 vs. that which was found on Sheet2.

What you are looking for is a feature called Conditional Formatting.

- Select the cells in Column A that you wish to apply the formatting to. I'm going to assume that your selection starts in A1.
- Click on Formatting...Conditional Formatting
- Select the first Drop Down and choose Formula Is...
- In the right hand field enter the following formula. (You can copy the formula from this post and paste it into the field with Ctrl-V. You can't paste it with a right-click, but Ctrl-V will work.)


- Click the Format button, then the Patterns tab and choose a color.

The Conditional Formatting will be applied to all of the cells that you have selected and update the formula automatically.


Whenever the condition spelled out by the formula is TRUE, the formatting will be applied.

In Excel 2003, you can have up to 3 Conditional Formats applied to a cell.

Report •

September 22, 2010 at 14:07:01
Thank you so much that worked like a charm - i am very much appreciative of not having to start a new string, it was intended for that same spreadsheet.

I have run into a snag with the formula results though. On some documents the returned rev value is a letter such as B, even though there is a rev 1 listed also. I think its because excel deems a letter to be greater than a number. Unfortunately for our purposes numbers rank higher than letters. what do you think i should do?

thanks :)

Report •

September 22, 2010 at 14:39:06
re: "what do you think i should do?"

I think you should standardize on a rev designation system of either numbers or letters.

Forget about how Excel handles letters vs. numbers, think about how people handle letters vs. numbers.

If I see:

2511-89 1
2511-89 A
2511-89 B
2511-89 3
2511-89 2
2511-89 C

How am I supposed to know which is the latest rev?

I'll work on a solution to handle this within Excel, but it's not going to happen right away.

BTW Which is a higher rev - letters or numbers?

Report •

September 22, 2010 at 18:37:07
This issue is that you are basically asking Excel to return the largest value in a sorted column, but you don't agree with Excel sort methods.

Excel sorts letters as "larger than" numbers because the ASCII code associated with letters is higher than that associated with numbers:

Value Range      ASCII Code Range
  0 - 9              48 - 57
  A - Z              65 - 90
  a - z              97 - 122

I don't know if your project is a one time exercise or if you will have to do this repeatedly but adding these manual steps might get you what you want:

In Sheet2 Column C enter this formula and drag it down:

=IF(ISTEXT(B1), CODE(B1)-125, B1)

If there is a letter in the cell, this will determine the ASCII code and "reduce" it to a negative number. If it's number, it will return the number so it ends up "higher" than a letter.

You can't Sort on the Formulas, so you need to do an Edit...PasteSpecial...Values on Column C to replace the formulas with the values.

Now, sort the entire table (Columns A:C) on Column C in descending order. Since the numbers will now be higher than the "adjusted letter codes", the numbers will end up in the highest positions and you can still use same VLOOKUP formula I suggested earlier.

Report •

September 23, 2010 at 04:48:00
Thats perfect :) I wish i had a school near here that would actually go into real world advanced applications like this.

You've been wonderful, thank you for all your help; this is really going to get the data that i need for the project.

Have a super great day! :)

Report •

Ask Question