Match value in one column and return value in same row in next col

Hi,

I have a a set of values in A1 through A100.  I need to look up each value 
and find a match in another set of values located in C1 through C200.  If a 
match is found then I need the formula located in column B to return the 
value in the same row but the next column over (D).

Example:

A        B             C      D
359                 375   10012
456                 125   10031
125   10031  952   10052
854                 444   10017


The formula located in B3 should find that the value of 125 (located in A3) 
matches the value in C2 and returns 10031 which is the value located in the 
next column over in the same row.

Thanks,

Zuo
0
Utf
3/6/2010 7:43:03 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
2469 Views

Similar Articles

[PageSpeed] 4

"Zuo" wrote:
> I have a a set of values in A1 through A100.
> I need to look up each value and find a match in
> another set of values located in C1 through C200.
> If a match is found then I need the formula located
> in column B to return the value in the same row but
> the next column over (D).

=IF(ISNUMBER(VLOOKUP(A1,$C$1:$D$200,2,0)),
VLOOKUP(A1,$C$1:$D$200,2,0), "")

Copy down through A100.

If you have Excel 2007, I believe this can be written more succinctly.  I do 
not have Excel 2007, but I believe the following syntax works:

=IFERROR(VLOOKUP(A1,$C$1:$D$200,2,0)), "")


----- original message -----

"Zuo" wrote:
> Hi,
> 
> I have a a set of values in A1 through A100.  I need to look up each value 
> and find a match in another set of values located in C1 through C200.  If a 
> match is found then I need the formula located in column B to return the 
> value in the same row but the next column over (D).
> 
> Example:
> 
> A        B             C      D
> 359                 375   10012
> 456                 125   10031
> 125   10031  952   10052
> 854                 444   10017
> 
> 
> The formula located in B3 should find that the value of 125 (located in A3) 
> matches the value in C2 and returns 10031 which is the value located in the 
> next column over in the same row.
> 
> Thanks,
> 
> Zuo
0
Utf
3/6/2010 8:13:01 PM
Thank you so much it works perfect!

"Joe User" wrote:

> "Zuo" wrote:
> > I have a a set of values in A1 through A100.
> > I need to look up each value and find a match in
> > another set of values located in C1 through C200.
> > If a match is found then I need the formula located
> > in column B to return the value in the same row but
> > the next column over (D).
> 
> =IF(ISNUMBER(VLOOKUP(A1,$C$1:$D$200,2,0)),
> VLOOKUP(A1,$C$1:$D$200,2,0), "")
> 
> Copy down through A100.
> 
> If you have Excel 2007, I believe this can be written more succinctly.  I do 
> not have Excel 2007, but I believe the following syntax works:
> 
> =IFERROR(VLOOKUP(A1,$C$1:$D$200,2,0)), "")
> 
> 
> ----- original message -----
> 
> "Zuo" wrote:
> > Hi,
> > 
> > I have a a set of values in A1 through A100.  I need to look up each value 
> > and find a match in another set of values located in C1 through C200.  If a 
> > match is found then I need the formula located in column B to return the 
> > value in the same row but the next column over (D).
> > 
> > Example:
> > 
> > A        B             C      D
> > 359                 375   10012
> > 456                 125   10031
> > 125   10031  952   10052
> > 854                 444   10017
> > 
> > 
> > The formula located in B3 should find that the value of 125 (located in A3) 
> > matches the value in C2 and returns 10031 which is the value located in the 
> > next column over in the same row.
> > 
> > Thanks,
> > 
> > Zuo
0
Utf
3/6/2010 9:28:01 PM
Reply:

Similar Artilces:

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

Auto Complete shows correct name but has wrong name in brackets next to it.
Exch 2K Win2K Server, Outlook 2k2 client Odd occurrance! On one user in our Exchange site, If a client has a contact with a similar name, the auto complete field shows the user's display name correctly but next to the name is another users name in brackets. Any way to fix this without blowing away the mailbox? Jim, Funny that you should bring this up. I have a couple of questions regarding the person who has the incorrect name in <brackets>. Was their name recently changed? Was this an old account in which was reused for a new user? I actually just found out today that the na...

extracting unique rows
Hi, I am trying to remove duplicate rows(consisting of 8 columns or horizontal cells). any ideas? thanks dan Data>Filter>Advanced Filter. Check "unique records" and "copy to another location" For more details and pictures see Debra Dalgleish's site http://www.contextures.on.ca/xladvfilter01.html#FilterUR Gord Dibben Excel MVP On Tue, 19 Apr 2005 17:00:01 -0700, DANmcc <DANmcc@discussions.microsoft.com> wrote: >Hi, >I am trying to remove duplicate rows(consisting of 8 columns or horizontal >cells). >any ideas? > >thanks > >...

MDI app with different CFrameWnd, searching one
Hello NG, in my MDI application, I use several different CChildFrame windows with its CDocument and CView. CPersonFrame* mit CPersonDoc und CPersonView CAdressesFrame* mit CAdressesDoc und CAdressesView CPreferencesFrame* mit CPreferencesDoc und CPreferencesView The PreferencesFrame and AdressesFrame should be opened only once at a time in my programme. The PersonFrame is a single window concerning each person in the AdressesFrame. Now I want to open a special window in my application menu. If it's already opened, it should appear in front. So I first look for the child window if it is...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

column charts on two axis
I am trying to show a column chart on 2 axis. I have chosen the combination line and column chart to get the 2 axis but when I change the line to a second column it puts the columns over the top of the first axis columns. How do I get them to be side by side? Have a look at: http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jo" <Jo@discussions.microsoft.com> wrote in message news:F3800842-2343-4319-8496-4D8CAC0DABFF@microsoft.com... >I am trying to show a column chart on 2 axi...

Change default search to Value, not formula?
Every time I start a word search, the default searches for fomulas instead of value. So everytime I have to open up the option button and change it to value instead of formula. How do I change that default setting? lbbss Saved from a previous post: Excel tries to help by remembering the last settings you used--except for the first search in that session. You can use that to your advantage. You could make a dummy workbook and put it in your xlStart folder. Have a macro in that workbook that does a find (and sets all the stuff the way you like). Then closes and gets out of the way. ...

Conditional format for unique values
Hello, I would like to set a conditional format to color each unique value a different color. It will also be applied to multiple occurences of the value. The problem is that the values are dynamic and may not always be the same number of occurences, in the same cell, etc. I have used the formula IF(ISNUMBER(COUNTIF($F$3:F3,F3)),ROW(),"")) to try to identify the occurences but creating a conditional format has proven out of my league! Please help! And thanks!! Conditional formats are limited to 3 different formats in XL2003 or less. So you can end up with a max of ...

Sum of every nth cell in a column
I am trying to figure out the formula for adding the nth cell in a given column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) That is an array formula so it must be committed with Shift + Ctrl + <Enter>. here is how it works... Moving down through cells C2:C213 it looks at each 2 things. The value of the cell and the row that cell is on. If the row that cell is on is +1 is evenly divisible by 1 then ...

Can one obtain Business Contact Manager Seperately
Does anybody know if an add on is available to add the "Business Contact Manager" to a copy of Outlook 2003 that came pre-installed on my new Dell. Apparently you have to buy either Office Pro or one other addition that I cannot re-call to get this feature. There has to be a way to add it to my current Outlook 2003. I purchased Office Basic along with this Dell and I really could use the "Business Contact Manager" tool. No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) vir...

How do I count one cell based on another cell?
How do you count the number of cells with data (not blank) in a given range based on a criteria in a different range? for example, how many corresponding cells are populated with data in c1:c1000, if b1:b1000 = SMU? I have tried so many variations of the the following to no avail. =COUNT(IF(b1:b1000,"SMU",counta(c1:c1000))) If anyone can resuce me...I am frustrated...what am I missing? Harr Hi try =SUMPRODUCT(--(B1:B1000="SMU"),--(C1:C1000<>"")) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany &qu...

Multiply all values by 10
Is there a way to multiply all values in my spreadsheet by 10? If so, how? Type the 10 in some random cell and copy. Select your target cells and Edit > Paste Special, and select the Multiply radio button. "RTimberlake" wrote: > Is there a way to multiply all values in my spreadsheet by 10? If so, how? First, make a copy of the workbook in case you make a typo or something, then put 10 in an empty cell, copy it, then select all values needed and do edit>paste special and select multiply -- Regards, Peo Sjoblom (No private emails please) "RTimberlake" ...

Converting hard returns
I have a linked table to sql server that uses the text datatype and in that datatype it has built in hard returns. when bringing them into access via a query they show up as a square character. Access is not recognizing them as a hard return and I need it to. Any help would be appreciated Hi Bob, Probably the "squares" are CR - Chr(13)- or LF - Chr(10) - characters; Access textboxes use the pair of characters CR+LF. Ordinarily (and with recent versions of Access) you can use the Replace() function in an update query, e.g. Replace([MyField], Chr(13), Chr(13) & Chr(10)) a...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

print only one page in a message Plain-Text
This is a multi-part message in MIME format. ------=_NextPart_000_014E_01C60ADA.6EC09160 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi everyone! I can print only one page when a received a message in HTML format, but, = when I received a message how Plain text, I can=B4t choose only one page = ... How can I to do this?? My outlook is 2003. ------=_NextPart_000_014E_01C60ADA.6EC09160 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4....

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

Change rows to columns for similar records
Hi - am trying to change rows to columns for similar records. How do I do that? e.g. Staff A Cellular No. XXXXX Staff A Office No. XXXX Staff B Cellular No. XXXXX Staff B Office No. XXXXX Wld like for it be in the following row Staff Cellular No Office No A XXXXX XXXXX B XXXXX XXXXXX Do advise. Thanks. one way would be Would be to set up your "heading" as such Cell Phone Staff A 1 2 Staff B 3 4 Then assuming that Staff A is in B8 Staff B is in B9 Cell is in C7 Phone is in D7 In cell c8 enter ...

if value is greater than # * by 20% if less * by 40
if price is higher than 30 dollars markup by 20% if less than 30$ markup by 40% Try this: For A1: (a price) The marked up price B1: =A1*IF(A1>30,1.2,1.4) Does that help? *********** Regards, Ron XL2002, WinXP "sypher" wrote: > if price is higher than 30 dollars markup by 20% if less than 30$ markup by 40% ...

formula for a column #2
I'm working on a daily worksheet where all the values for a column will be the same ex. L2/C2, L3/C3, L4/C4 How do I set it up? Assuming the column you want to compute the values is in say, col M Put in M2: =IF(C2=0,"",L2/C2) and press ENTER Point the cursor to the bottom right corner of M2 (cursor will turn into a "black cross") Drag to fill as far down as required .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "msdobe" <msdobe@discussions.microsoft.com> wrote in message news:529B3703-9D9A-4560-80D...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

This forum does have value!
As year end approaches and I am considering upgrading to Money 2005, I found the message traffic extremely valuable. After this summers fiasco with the update to MSN Money locking us out of our local data file, I thought, or was hoping M$ would make an extra effort to make sure that Money 2005 was a significant improvement and without the assorted problems that I have found posted here. Some still unresolved. I have made my decision and I do not care how much work it is going to take me, bt I am going back to Quicken after giving Money a two year trial. This program is just not ready f...

Column heading differences
The menu column headings on my spreadsheets today are numbers instead of letters! I want the letters back! I like the differentiation between rows (numbers) and columns (letters). Help?? I received and opened a virus-free spreadsheet from a co-worker. Her spreadsheet had numbers for the columns -- could that have made my default switch? How do I switch it back? <Tools> <Options> <General> tab, And *UNCHECK* "R1C1 Reference Style". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benef...