Return Corresponding Value Based on Comparing Two Sheets of Data

Can someone please help me find a formula (or two) for this example. If you 
can show me a couple of ways to do this (so I can learn), I'd greatly 
appreciate it! Thanks!

I'm trying to figure out a formula (or two) that will help me auto-populate 
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the 
"8888" row to return blank, since it does not exist in Sheet 1. 

Sheet 1:

ColumnA  ColumnB

1234        20
4321        10
5678        11
8765         -
9999        12
7777        13

Sheet 2:

ColumnA  ColumnB

5678        ?
8765        ?
1234        ?
4321        ?
8888        ?
0
Utf
3/12/2010 5:06:07 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
751 Views

Similar Articles

[PageSpeed] 17

>Sheet 1:
>ColumnA  ColumnB
>8765         -

Is that "dash" entered in the cell or does it represent an empty cell?

>Sheet 2:
>ColumnA  ColumnB
>8765        ?

So, what result should appear on Sheet2 for 8765?

-- 
Biff
Microsoft Excel MVP


"PaulQ" <PaulQ@discussions.microsoft.com> wrote in message 
news:C5F93E5F-6C54-4354-9891-5C4C3E360A58@microsoft.com...
> Can someone please help me find a formula (or two) for this example. If 
> you
> can show me a couple of ways to do this (so I can learn), I'd greatly
> appreciate it! Thanks!
>
> I'm trying to figure out a formula (or two) that will help me 
> auto-populate
> the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
> "8888" row to return blank, since it does not exist in Sheet 1.
>
> Sheet 1:
>
> ColumnA  ColumnB
>
> 1234        20
> 4321        10
> 5678        11
> 8765         -
> 9999        12
> 7777        13
>
> Sheet 2:
>
> ColumnA  ColumnB
>
> 5678        ?
> 8765        ?
> 1234        ?
> 4321        ?
> 8888        ? 


0
T
3/12/2010 5:23:34 AM
This should do what you want:
=if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlookup(a1,sheet1!A:B,2,false))

Regards,
Fred

"PaulQ" <PaulQ@discussions.microsoft.com> wrote in message 
news:C5F93E5F-6C54-4354-9891-5C4C3E360A58@microsoft.com...
> Can someone please help me find a formula (or two) for this example. If 
> you
> can show me a couple of ways to do this (so I can learn), I'd greatly
> appreciate it! Thanks!
>
> I'm trying to figure out a formula (or two) that will help me 
> auto-populate
> the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
> "8888" row to return blank, since it does not exist in Sheet 1.
>
> Sheet 1:
>
> ColumnA  ColumnB
>
> 1234        20
> 4321        10
> 5678        11
> 8765         -
> 9999        12
> 7777        13
>
> Sheet 2:
>
> ColumnA  ColumnB
>
> 5678        ?
> 8765        ?
> 1234        ?
> 4321        ?
> 8888        ? 

0
Fred
3/12/2010 5:36:09 AM
The dash is an actual value. That is, the value returned for row 8765 should 
be "-". Thanks!

"T. Valko" wrote:

> >Sheet 1:
> >ColumnA  ColumnB
> >8765         -
> 
> Is that "dash" entered in the cell or does it represent an empty cell?
> 
> >Sheet 2:
> >ColumnA  ColumnB
> >8765        ?
> 
> So, what result should appear on Sheet2 for 8765?
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "PaulQ" <PaulQ@discussions.microsoft.com> wrote in message 
> news:C5F93E5F-6C54-4354-9891-5C4C3E360A58@microsoft.com...
> > Can someone please help me find a formula (or two) for this example. If 
> > you
> > can show me a couple of ways to do this (so I can learn), I'd greatly
> > appreciate it! Thanks!
> >
> > I'm trying to figure out a formula (or two) that will help me 
> > auto-populate
> > the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
> > "8888" row to return blank, since it does not exist in Sheet 1.
> >
> > Sheet 1:
> >
> > ColumnA  ColumnB
> >
> > 1234        20
> > 4321        10
> > 5678        11
> > 8765         -
> > 9999        12
> > 7777        13
> >
> > Sheet 2:
> >
> > ColumnA  ColumnB
> >
> > 5678        ?
> > 8765        ?
> > 1234        ?
> > 4321        ?
> > 8888        ? 
> 
> 
> .
> 
0
Utf
3/12/2010 6:28:01 AM
That worked perfectly! Thanks, Fred!

Bill, feel free to give your suggestions as well. Again, as a learning 
opportunity for me (and others) and for a different perspective. Thanks! You 
guys are great!

"Fred Smith" wrote:

> This should do what you want:
> =if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlookup(a1,sheet1!A:B,2,false))
> 
> Regards,
> Fred
> 
> "PaulQ" <PaulQ@discussions.microsoft.com> wrote in message 
> news:C5F93E5F-6C54-4354-9891-5C4C3E360A58@microsoft.com...
> > Can someone please help me find a formula (or two) for this example. If 
> > you
> > can show me a couple of ways to do this (so I can learn), I'd greatly
> > appreciate it! Thanks!
> >
> > I'm trying to figure out a formula (or two) that will help me 
> > auto-populate
> > the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
> > "8888" row to return blank, since it does not exist in Sheet 1.
> >
> > Sheet 1:
> >
> > ColumnA  ColumnB
> >
> > 1234        20
> > 4321        10
> > 5678        11
> > 8765         -
> > 9999        12
> > 7777        13
> >
> > Sheet 2:
> >
> > ColumnA  ColumnB
> >
> > 5678        ?
> > 8765        ?
> > 1234        ?
> > 4321        ?
> > 8888        ? 
> 
> .
> 
0
Utf
3/12/2010 6:38:01 AM
You're welcome. Thanks for the feedback.

Regards,
Fred

"PaulQ" <PaulQ@discussions.microsoft.com> wrote in message 
news:EC323E39-66CA-4A42-AD1C-CA523271720E@microsoft.com...
> That worked perfectly! Thanks, Fred!
>
> Bill, feel free to give your suggestions as well. Again, as a learning
> opportunity for me (and others) and for a different perspective. Thanks! 
> You
> guys are great!
>
> "Fred Smith" wrote:
>
>> This should do what you want:
>> =if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlookup(a1,sheet1!A:B,2,false))
>>
>> Regards,
>> Fred
>>
>> "PaulQ" <PaulQ@discussions.microsoft.com> wrote in message
>> news:C5F93E5F-6C54-4354-9891-5C4C3E360A58@microsoft.com...
>> > Can someone please help me find a formula (or two) for this example. If
>> > you
>> > can show me a couple of ways to do this (so I can learn), I'd greatly
>> > appreciate it! Thanks!
>> >
>> > I'm trying to figure out a formula (or two) that will help me
>> > auto-populate
>> > the ? fields within ColumnB of Sheet 2. Also, I want the ? value for 
>> > the
>> > "8888" row to return blank, since it does not exist in Sheet 1.
>> >
>> > Sheet 1:
>> >
>> > ColumnA  ColumnB
>> >
>> > 1234        20
>> > 4321        10
>> > 5678        11
>> > 8765         -
>> > 9999        12
>> > 7777        13
>> >
>> > Sheet 2:
>> >
>> > ColumnA  ColumnB
>> >
>> > 5678        ?
>> > 8765        ?
>> > 1234        ?
>> > 4321        ?
>> > 8888        ?
>>
>> .
>> 

0
Fred
3/12/2010 4:36:28 PM
>show me a couple of ways to do this (so I can learn)

Ok, here'a bunch of examples. This is a good demonstration that shows just 
how many different ways you can do something.

Let's assume your data is setup like this:

A2:B7 -

1234        20
4321        10
5678        11
8765         -
9999        12
7777        13

A10:A14 -

5678
8765
1234
4321
8888

Enter any one of these formulas in B10 and copy down to B14:

=IF(ISNA(VLOOKUP(A10,A$2:B$7,2,0)),"",VLOOKUP(A10,A$2:B$7,2,0))

=IF(ISERROR(VLOOKUP(A10,A$2:B$7,2,0)),"",VLOOKUP(A10,A$2:B$7,2,0))

=IF(ISNA(MATCH(A10,A$2:A$7,0)),"",VLOOKUP(A10,A$2:B$7,2,0))

=IF(ISERROR(MATCH(A10,A$2:A$7,0)),"",VLOOKUP(A10,A$2:B$7,2,0))

=IF(ISNUMBER(MATCH(A10,A$2:A$7,0)),VLOOKUP(A10,A$2:B$7,2,0),"")

=IF(COUNT(MATCH(A10,A$2:A$7,0)),VLOOKUP(A10,A$2:B$7,2,0),"")

=IF(COUNTIF(A$2:B$7,A10),VLOOKUP(A10,A$2:B$7,2,0),"")

=IF(ISNA(MATCH(A10,A$2:A$7,0)),"",INDEX(B$2:B$7,MATCH(A10,A$2:A$7,0)))

=IF(ISERROR(MATCH(A10,A$2:A$7,0)),"",INDEX(B$2:B$7,MATCH(A10,A$2:A$7,0)))

=IF(ISNUMBER(MATCH(A10,A$2:A$7,0)),INDEX(B$2:B$7,MATCH(A10,A$2:A$7,0)),"")

=IF(COUNT(MATCH(A10,A$2:A$7,0)),INDEX(B$2:B$7,MATCH(A10,A$2:A$7,0)),"")

=IF(COUNTIF(A$2:A$7,A10),INDEX(B$2:B$7,MATCH(A10,A$2:A$7,0)),"")

=IF(COUNTIF(A$2:A$7,A10),OFFSET(B$2,MATCH(A10,A$2:A$7,0)-1,0),"")

=IF(ISNA(MATCH(A10,A$2:A$7,0)),"",OFFSET(B$2,MATCH(A10,A$2:A$7,0)-1,0))

=IF(ISERROR(MATCH(A10,A$2:A$7,0)),"",OFFSET(B$2,MATCH(A10,A$2:A$7,0)-1,0))

=IF(ISNUMBER(MATCH(A10,A$2:A$7,0)),OFFSET(B$2,MATCH(A10,A$2:A$7,0)-1,0),"")

=IF(COUNT(MATCH(A10,A$2:A$7,0)),OFFSET(B$2,MATCH(A10,A$2:A$7,0)-1,0),"")

I might have missed a few!

-- 
Biff
Microsoft Excel MVP


"PaulQ" <PaulQ@discussions.microsoft.com> wrote in message 
news:BC9C41B1-81EC-48EE-B9B1-7EDED91F379A@microsoft.com...
> The dash is an actual value. That is, the value returned for row 8765 
> should
> be "-". Thanks!
>
> "T. Valko" wrote:
>
>> >Sheet 1:
>> >ColumnA  ColumnB
>> >8765         -
>>
>> Is that "dash" entered in the cell or does it represent an empty cell?
>>
>> >Sheet 2:
>> >ColumnA  ColumnB
>> >8765        ?
>>
>> So, what result should appear on Sheet2 for 8765?
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "PaulQ" <PaulQ@discussions.microsoft.com> wrote in message
>> news:C5F93E5F-6C54-4354-9891-5C4C3E360A58@microsoft.com...
>> > Can someone please help me find a formula (or two) for this example. If
>> > you
>> > can show me a couple of ways to do this (so I can learn), I'd greatly
>> > appreciate it! Thanks!
>> >
>> > I'm trying to figure out a formula (or two) that will help me
>> > auto-populate
>> > the ? fields within ColumnB of Sheet 2. Also, I want the ? value for 
>> > the
>> > "8888" row to return blank, since it does not exist in Sheet 1.
>> >
>> > Sheet 1:
>> >
>> > ColumnA  ColumnB
>> >
>> > 1234        20
>> > 4321        10
>> > 5678        11
>> > 8765         -
>> > 9999        12
>> > 7777        13
>> >
>> > Sheet 2:
>> >
>> > ColumnA  ColumnB
>> >
>> > 5678        ?
>> > 8765        ?
>> > 1234        ?
>> > 4321        ?
>> > 8888        ?
>>
>>
>> .
>> 


0
T
3/12/2010 5:23:34 PM
Reply:

Similar Artilces:

error in importing data
Sorry the error is 0x8004032d ...

Re: Extracting Exchange User Data From AD 02-24-10
If you do not like scripting, you can try GAL Exporter or Fast User Manager & Reports from IMIBO - http://www.imibo.com > > "Ringholz, Blake" <bringholz@nospam.com> wrote in message > news:76AEFC2F-85A7-4666-8262-27FB0737D09A@microsoft.com... >> Hello All - >> >> I need to get an Excel Spreadsheet that lists everyone first name, last >> name, email address, job title, etc pulled from Active Directory. Is >> there an easy way to do this? >> >> Thanks, >> Blake > > > > > ...

Message "class not registered" opening sheet with macros Excel 97
I have created a document with Macros and is password protected. I have sent it to several people who are able to use it - the one who can't is using Excel 97 SP2. They are getting error message "class not registered" - could it be the version they are using and how do I make sure they are able to use it. This could simply be a version issue if you developed on a later version and used controls from that versions object libraries. You should always use the lowest version to develop on. If this is not the case look in the VBE on the faulty machine and check tools>ref...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

SCHTask.exe returns "Failed to start" from SVR 2008
Hi, We have a number of 2003 servers which run lots of scheduled tasks around the clock. We also have some 2008 servers which do the same. We use SCHTask.exe from the 2008 servers, to query the scheduled tasks on the 2003 servers, and then look through the output of this query for errors, which is then used elsewhere to alert us that a scheduled task has failed on a given machine, and what the task is called. The problem we are having is that if a scheduled task runs sucessfully on a 2003 server, but has a NON ZERO return code, when SCHTask is run from a 2008 server and quer...

Doing Analysis from large amounts of DATA
Hi all, I have an attachment that's apart of this message & within it I have 3 tabs 1) Revenue 2) Expenses & 3) Net Position. I would like to do some Analysis on the above three thou a way where I can manipulate the Data. How would I go about doing this? I've tried a Pivot Table thou it didn�t work out in the sense that it was messy & just failed in calculating stuff, not sure if I did it right thou.. Would really appreciate all the help as I actually have about 20 of these sheets which from that fall into 4 groups being 5 sheets per group. I hope this all makes sense, wo...

Excel 2002
Have several spreadsheet files I use routinely. Three have recently crashed after I added another sheet. In each case the document recovery created a file missing all the color and text formats that the file contained before the crash. Not sure what other changes may have occurred. Is there something wrong with the copy of Excel on my PC? Could these three files be corrupt? Is there a procedure to "clean-up" these files? Thanks in advance for any suggestions. Mark Hi sounds like they are corrupted. I would suggest to copy the data + formats to a new, 'fresh' workbook. ...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle vendor, one called problem type and the rows are labeled and sorted b date. I would like to sum the hours lost column for each month according t the month and vendor, and have the sum end up in one cell I would also like to sum the hours lost column for each month accordin to the month and problem type and have the sum end up in one cell basically I only want the hours lost data for a specific vendor an month at one time or a specific problem type and month at one time, bu I don't know how to set up the formula correctl...

How do I display two excel pages at the same time?
I want to have two excel pages displayed on my computer at the same time, but when I open both pages it uses the same master excel and when I toggle between the two it won't open both of them up. I'm using office 2003. Thanks for the help. -- snowtime ------------------------------------------------------------------------ snowtime's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25982 View this thread: http://www.excelforum.com/showthread.php?threadid=393499 Open both and then do windows>arrange and vertical (or any of the other choices, I usuall...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

Made two versions; both run very slow . Any spare nitro around
I've been recording some macros to append together, but put the following together by writting it (well in my case it's a bitsa from all the good code on this site). However, it runs very slow. Every 3 letter code in cells down column("K") needs to be checked to see if the same is found down column("A"). As a check, I've just been putting a "1" in the same row in column("N"). Latter columns("K:N") can then be sorted on column("N"). Column("A") has 3 letter codes from row 3 to 1402 (but changes each run). Colu...

Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a pivot table thus displaying a charted image of the detail item selected. Any suggestions? ...

Importing data into Outlook #2
Is there a process or program with which I can transfer my ACT! data into Outlook contacts? Thanks! ...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart that contains a horizontal target line. Can anyone help me? Hi, I tried it in both 2007 and 2003 and had no problems. We need more infomation, can you post a sample of the chart somewhere. The only issue I have is that the target line is displayed on the data table, which I would prefer that it not be. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...

Sheet display vrs. Print Preview
Why would a sheet display differently than when it is printed. I have an excel file that looks like it prints on my bosses computer but on my pc the page breaks are different the word warps are different. This makes it difficult to work with because it will not print the same on my pc and his. We are both using Excel 2003. Thanks for helping. This could have a great deal to do with the printer drivers. Are you printing to the same printer? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Byron" <Byron@discussions.microsoft.com> wrote in mes...

need to make a formula that would add a field value to current dat
I have made a form in which I input different values. On of the values is (How Many Days). Now I need to a assign a default value, or expression (not sure which way to go about this) that will take the date value for (Date) and add the value (How Many Days) I figured that the formula should read =sum([Date]+[How Many Days]) But that is not giving me any results, thanx for your help in advance =DateDiff("d", Date(), [How Many Days]) -- Wayne Manchester, England. "J Man" wrote: > I have made a form in which I input different values. On of the values is ...

Output the list of frequent data
Dear all, Here comes two problems. Problem 1: I have a list of strings (say, in the column A1:A100). How can I find the "mode" (i.e. the string appearing most frequently in the list? For example, if the list is APPLE APPLE BANANA APPLE CREAM CREAM BANANA CREAM APPLE DONUT then I want the result is APPLE. It seems that the MODE function does not suppot data type other than numbers. Is there any canned UDF for it? Problem 2: Following Problem 1, I want to generate a list of the 3 most frequent data in the list. If the list is the one in the example, I want to list to be...

Data Validation #47
kshave multiple identacle sheets in a workbook(31 sheets). I am trying to allow users to only enter whole numbers in some of these cells. I know I can do it through Data /Validation/Allow - but, I want to do it on all 31 sheets at once. When I select all sheets - Validation is greyed out. Should I be using a different method? Thanks, Tom Apply the data validation on Sheet1 Copy the cell(s) in which you applied data validation Select Sheet2 Scroll to see sheet31 Hold the Shift key, and click on Sheet31 tab Select the cell where you want to paste the data validation Choose Edit>Paste Cl...

Evaluate Yes/No Field Based on User Input
Hi. I have a field that is set to Yes/No. I want to ask the user a question and based on their response (whether they type yes or no) I want the query to check the field and return all records marked yes is they type yes and all other records if they type no. How can I do this? Also, could I present them with a simple text box (having yes and no choices) or maybe a check box so they won't have to type anything? If you help me with the first part, this question is a bonus. I'll be happy with just the first question answered. Thanks! A Yes/No field actually stores -...

Access Outlook Add-in for Data Collection & Publishing won't load
I am trying to utilize the Collect Data via e-mail function and keep receiving the following error: "The Microsoft Office Access Outlook Add-in is disabled in Microsoft Office Outlook 2007. To collect data by using e-mail messages in Microsoft Access 2007, verify that this add-in is installed and enabled from within Office Outlook 2007." I have tried to remediate via the Trust Center/Add-in area in Outlook and don't see an add-in for Access (even though the error message says it is "disabled" it is nowhere to be found). I have tried to add it (I believe it is...

Modifing data to show up better in chart.
My Y1 axis is from 0-250 my Y2 axis is from 0-9000 When graphing percentages the line shows up at the very bottom since 99% < 1 is there a way I can multiply these percentages by 100 to show them as 9900% = 99 Normally you would just make then a 2nd axis but as you can see I already have a second axis of 0-9000 In article <A9E6F75B-7307-4291-BC7D-729F9DF88D50@microsoft.com>, MikePunko@discussions.microsoft.com says... > My Y1 axis is from 0-250 my Y2 axis is from 0-9000 > When graphing percentages the line shows up at the very bottom since 99% < 1 > is there a way I...

column value translation
I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next...