If Cells Match in Two Columns, Then

[ipperz]

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
	A	B	C	D
1	List 1	Amounts		List 2
2	Option 1	5		n/a 1
3	Option 2	10		n/a 2
4	Option 3	15		Option 1
5	Option 4	20		n/a 3
6	Option 5	25		n/a 4
Sheet1

Thank you for any insights!

 

[etaf]

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

 

[RasGhul]

Hi ipper,

maybe this?

Book1
	A	B	C	D	E
1	List 1	Amounts		List 2
2	Option 1	5		n/a 1
3	Option 2	10		n/a 2
4	Option 3	15		Option 1	5
5	Option 4	20		n/a 3
6	Option 5	25		n/a 4
Sheet1

Cell Formulas
Range	Formula
E2	=IFERROR(INDEX($B$2:$B$6,MATCH(D2,$A$2:$A$6,0)),"")

 

[Joe4]

As long as you only ever have one entry in column D with "Option...", this formula should work:

=INDEX(B2:B6,MATCH(INDEX(D2:D6,MATCH("*Option*",D2:D6,0)),A2:A6,0))

(and it will work from any cell).

 

[ipperz]

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
	A	B	C	D
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
Sheet1

Thanks again for any insights!

 

[Joe4]

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.

 

[ipperz]

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.

 

[Joe4]

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.

 

[JustSomeGuy]

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.