Report

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

Ask a question ramdubai 2Posts Monday March 11, 2013Registration date March 11, 2013 Last seen - Last answered on Sep 7, 2016 at 02:30 AM by 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.
See more 
Helpful
+7
plus moins
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?  
Wafaa- Mar 2, 2014 at 02:29 PM
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 42Posts 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- 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
Reply
prabhu- Feb 26, 2016 at 02:13 AM
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
Helpful
+0
plus moins
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.
venkat- Jul 4, 2016 at 07:42 AM
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
Helpful
+0
plus moins
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
Helpful
+0
plus moins
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?

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!