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

ramdubai 2 Posts Monday March 11, 2013Registration date March 11, 2013 Last seen - 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 
2Posts Monday March 11, 2013Registration date March 11, 2013 Last seen

11 replies

Kevin@Radstock 42 Posts Thursday January 31, 2013Registration date April 26, 2014 Last seen - Mar 11, 2013 at 02:57 AM
+3
Helpful
6
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
Was this answer helpful?  
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?
Kevin@Radstock 42 Posts Thursday January 31, 2013Registration date April 26, 2014 Last seen - Mar 3, 2014 at 12:39 AM
Hi

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