Related:

- Excel formula to compare two columns and return a value
- Excel match two columns and output third - Best answers
- If two cells match, return value from third - Best answers
- Comparing columns and returning value ✓ - Forum - Excel
- Need a formula to compare two columns and return a value of 1 - Forum - Excel
- Compare two columns and return text string - Forum - Excel
- Comparing two columns of names in Excel ✓ - Forum - Excel
- Compare two columns input value from another ✓ - Forum - Excel

Best answer

Kevin@Radstock
Mar 11, 2013 at 02:57 AM

- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Last seen
- April 26, 2014

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

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

4306 users have said thank you to us this month

ramdubai
Mar 11, 2013 at 10:02 AM

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

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.

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
Mar 11, 2013 at 11:40 AM

- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Last seen
- April 26, 2014

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

megmoto
Jan 17, 2014 at 03:40 AM

- Posts
- 1
- Registration date
- Friday January 17, 2014
- 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?

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

Could you advise?

Perhaps something along these lines.

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

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

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