Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

Compare values in two columns and return the value from third [Solved/Closed]

Report
Posts
2
Registration date
Monday March 11, 2013
Status
Member
Last seen
March 11, 2013
 -
 keshav -
Hello,

I've a table containing four columns. If the values in the first two columns match to particular value (eg. A1="xxxx" and B1="yyy"), I need to return the value in the third column to the fourth column.
Can anybody help??? Thanks in advance.
Related:

4 replies

Answer 1 / 4
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
Hi ramdubai

Are the criteria values unique! If they are, try the following. assuming your data is in A1:C100 including column headers.

=LOOKUP(2,1/((A2:A1000=Value1)*(B2:B1000=Value2)),C2:C1000)

If there are multiple criteria which match, then try the array formula ("ctrl + shift + enter" to commit)

=IFERROR(INDEX($C$2:$C$1000,SMALL(IF(($A$2:$A$1000=Value1)*($B$2:$B$1000=Value2),ROW($2:$1000)-ROW($1:$1)),ROW($A1))),"")

Kevin
3
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

View all 6 comments
Report

I have two columns A and B I want to match them, when they do match at a cell I want to return a value from a third column to a fourth column..the value needed from the third column is in the same row as the matched value in column B how can I do that please?
Report
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
Hi

Perhaps something along these lines.
=IF(A1=B1,C1,"")
Report
>
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
sir
if i have a column a and in column we have two or more same no.(like ab-230,ab-230),randomaly. i want to search them and add there value.
ex:
A B C
ab-230 2 4
an-25 4 7
ab-230 3 6

we want to add column a like

A b C
ab-230 5 10

pls reply me
Report

Hi

I have two an excel with 3 sheets. One sheet has desc column, another sheet has id column and desc column. now I have to compare two descs and fetch the id value in another column.

example

zone1 zone2 id
hyd blr 1 3
hyd hyd 2 1
blr che 3 4
che tec 4 0

if zone1 value wherever occur in the zone2, then corresponding id value has to display in D column .


Please advise.

Regards
Prabhakar
Answer 2 / 4
Posts
2
Registration date
Monday March 11, 2013
Status
Member
Last seen
March 11, 2013
Hi Kevin,

thank you very much for the reply. That was quite useful.

However, please find below the table I was referring to.

Weld Area Welder# Acceptance

Shell W-001 ACC
Base Pl W-002 REP
shell-1 W-003 ACC
shell-2 W-004 REP
shell-3 W-001 REP
shell-4 W-006 ACC
roof-1 W-003 ACC
roof-2 W-005 REP
girder-2 W-007 REP
girder-2 W-008 REP

I need to return all values in column A to a different sheet where the value in column#2="W-001" and column#3="REP" and so-on.

Thanks for your help again.
Report

Write a formula in cells F22 & F23 to display Top performing AdUnit Name based on the impressions & CTR from Table 1. Plg tell me one example


AdUnit Name Impressions CTR Total VBCs VBC Rate Total C2Cs C2C Rate
Collection - Intent 30,658 0.49% 163 0.53% 15 10.07%
Product - Intent 171,897 0.40% 795 0.46% 83 11.96%
acoach-category-t2-intent 53,163 0.34% 183 0.34% 18 9.84%
Category - Intent 74,346 0.32% 240 0.32% 19 7.95%
Category - No Intent 32,910 0.22% 16 0.05% 6 8.33%
Collection - No Intent 32,384 0.50% 18 0.06% 3 5.08%
Product - No Intent 27,492 0.18% 10 0.04% 1 2.00%
Pixel Tracking 2,609,661 0.21% 71 0.00%
Grand Total 3,032,511 0.05% 1,496 0.05% 145 10.03%





Metrics Top Performing AdUnitName
Impressions
CTR
Answer 3 / 4
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
Hi ramdubai

As I said in my previous post, if there is more then one value to return, then use the second formula. You will need to change the formula as in the first post you wanted to return a value from the third column.

=IFERROR(INDEX(Sheet1!$A$2:$A$1000,SMALL(IF((Sheet1!$B$2:$B$1000="W-001")*(Sheet1!$C$2:$C$1000="REP"),ROW(Sheet1!$2:$1000)-ROW(Sheet1!$1:$1)),ROW($A1))),"")

Array formula (CTRL + SHIFT + ENTER to commit) and copy down. You will need to adjust your columns, rows and sheet to suit your requirment.

So with your example, there is only 1 match for "W-001" & "REP": shell-3

Kevin
Answer 4 / 4
Posts
1
Registration date
Friday January 17, 2014
Status
Member
Last seen
January 17, 2014
Hi Kevin - this formula is exactly what I need in my sheet - I've replicated the above example and it works perfectly - but in my own sheet the data is located in columns B, C & F with the header rows at Row 4. I cant figure out how I can transpose the above formula into my own sheet. I'm guessing it is something to do with the ROW elements.

I am looking to check the criteria in C & F and then return the value in B

Could you advise?
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!