- Compare values in two columns and return the value from third
- If either of two cells are less than a figure then return a value
- Comparing columns and returning value (Solved)
- Compare two Columns and return a value
- Compare two columns and return text string
- Comparing data in two columns and setting a value
- Compare two columns with another two columns and return

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

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?

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.

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

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

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

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

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?

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.

Wafaa- Mar 2, 2014 at 02:29 PMPerhaps something along these lines.

=IF(A1=B1,C1,"")

keshav- Sep 7, 2016 at 02:30 AMif 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

Babor- Aug 28, 2015 at 02:00 PMprabhu- Feb 26, 2016 at 02:13 AMI 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