If Cells Match in Two Columns, Then

ipperz

New Member
Joined
Feb 1, 2018
Messages
12
Hello,

I am trying to come up with a formula that will review two lists (A2:A6 and D2:D6), and if any cells in the lists match, then return the value of that match from Column B. There would not be multiple answers to this (Option 1 and Option 2 would not be listed in D2:D6). Example below: "Option 1" matches both lists, so the return value should be "5".


Excel 2016 (Windows) 32 bit
ABCD
1List 1AmountsList 2
2Option 15n/a 1
3Option 210n/a 2
4Option 315Option 1
5Option 420n/a 3
6Option 525n/a 4
Sheet1


Thank you for any insights!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
you could use a countif() >0 combined with a lookup

in e2

=IF( Countif($d$2:$d$6, A2)>0, B2,"")
and copy down , but i'm not sure thats what you want
can you give more examples and the results expected
 
Upvote 0
Hi ipper,

maybe this?


Book1
ABCDE
1List 1AmountsList 2
2Option 15n/a 1 
3Option 210n/a 2
4Option 315Option 15
5Option 420n/a 3
6Option 525n/a 4
Sheet1
Cell Formulas
RangeFormula
E2=IFERROR(INDEX($B$2:$B$6,MATCH(D2,$A$2:$A$6,0)),"")
 
Upvote 0
As long as you only ever have one entry in column D with "Option...", this formula should work:
Code:
=INDEX(B2:B6,MATCH(INDEX(D2:D6,MATCH("*Option*",D2:D6,0)),A2:A6,0))
(and it will work from any cell).
 
Last edited:
Upvote 0
Thank you all for posting. I think I may have been incorrect in my original message, so i'll start over with the below example.

List 1 is a list of things that a user inputs (can be anything [phone 1, pay code 2, etc], so searching for specific text won't help).

List 2 is another list of different texts. In the below example, there happens to be a match with a user input from Column A and in Column C. That's great! Now return the value in Column D of that match. I'm trying to avoid creating extra column and dragging down, as there should only be one answer (the match's value in Column D).


Excel 2016 (Windows) 32 bit
ABCD
1List 1List 2Amounts
2catn/a 15
3dogn/a 210
4turtlebird15
5birdn/a 320
6mousen/a 425
Sheet1


Thanks again for any insights!
 
Upvote 0
I am confused. The data structure of your example totally changed. The amounts were in column B before. Now they are in column D.
So, where exactly do you want to return the number of the matching value?

I think what you want to do can be done with a simple VLOOKUP, but you would need to check each value in column A separately.
 
Last edited:
Upvote 0
Right, the first example was incorrect and I was starting over with example 2. Column D is the value needed.

I figured it may have to be a VLOOKUP or INDEX/MATCH searching for each value in Column A separately, but I was hoping to find a more condensed way. It would be nice to do something like:

=INDEX(D2:D6,MATCH(A5,C2:C6,0)) but where A5 in the formula observed A2:A6 or did an OR() match.

If not, it may just need to be the above for each in Column A.
 
Upvote 0
With VLOOKUP and MATCH, you are looking up a single item at a time.
I am not sure how to do all that you want in one formula.
I would love to see if someone could do it.
 
Upvote 0
You stated above there would not be multiple answers, if this is the case, then can't we just use a simply VLookup Formula?

1 List 1 List 2 Amounts
2 cat n/a 1 5
3 dog n/a 2 10
4 turtle bird 15
5 bird n/a 3 20
6 mouse n/a 4 25

=VLOOKUP($D1,$B$1:$E$6,4,FALSE) and just drag down? By the time it gets to "bird" it should equal 20.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top