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.

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)

There will be duplicate drawing numbers on Sheet 2 but duplicate revs Sheet could have:

1552-8999 1

1552-8999 0

1552-8999 2

re: " There will be duplicate drawing numbers on Sheet 2 but duplicate revsDo you mean duplicate numbers but

differentrevs?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?

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 0Does that help?

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_valuein thelookup_array, it will find return the highest rev level kind of "on accident".

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 :)

It should, assuming your lookup_arrayis in A1:B10. Adjust as required.If you plan to use VLOOKUP (or

anyExcel 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.

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 1In 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 :)

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.)=IF(B1>C1,TRUE,FALSE)

- 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.

=IF(B2>C2,TRUE,FALSE)

=IF(B3>C3,TRUE,FALSE)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.

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 :)

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

peoplehandle letters vs. numbers.If I see:

2511-89 1

2511-89 A

2511-89 B

2511-89 3

2511-89 2

2511-89 CHow 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?

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 - 122I 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.

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! :)

Ask Your Question

Weekly Poll