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

Posts
2
Registration date
Monday March 11, 2013
Last seen
March 11, 2013
- Mar 11, 2013 at 01:34 AM - Latest reply:  keshav
- Sep 7, 2016 at 02:30 AM
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.
See more 

11 replies

Best answer
Posts
42
Registration date
Thursday January 31, 2013
Last seen
April 26, 2014
- Mar 11, 2013 at 02:57 AM
3
Thank you
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

Thank you, Kevin@Radstock 3

Something to say? Add comment

CCM has helped 1633 users this month

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?
Posts
42
Registration date
Thursday January 31, 2013
Last seen
April 26, 2014
- Mar 3, 2014 at 12:39 AM
Hi

Perhaps something along these lines.
=IF(A1=B1,C1,"")
keshav >
Posts
42
Registration date
Thursday January 31, 2013
Last seen
April 26, 2014
- Sep 7, 2016 at 02:30 AM
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
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
Posts
2
Registration date
Monday March 11, 2013
Last seen
March 11, 2013
- Mar 11, 2013 at 10:02 AM
0
Thank you
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.
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
Posts
42
Registration date
Thursday January 31, 2013
Last seen
April 26, 2014
- Mar 11, 2013 at 11:40 AM
0
Thank you
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
Posts
1
Registration date
Friday January 17, 2014
Last seen
January 17, 2014
- Jan 17, 2014 at 03:40 AM
0
Thank you
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?