Finding an exact match from multiple options

[orion23]

Hello everyone... Hope you are all doing well...

I'd like to ask for help with a formula to achieve the following:

Column A has a unique identifier and column B will list a specific value (text)

The formula will have to match the value in "A" against "T" where it will be listed about 8 times in a list, and match "B" against "U", again, where the list might have 8 options but only 1 will match "B"

In other words, find A and B, and match both in a list in T and U, giving the value of column "V"


Thank you for your time.

[AliGW]

Please attach a sample workbook.

[orion23]

Of course.
Thank you

[C.Yeti]

I think my question is similar to this one, If i'm understanding Orion's question.

I have a list of people. Each person has a number associated with their name. I need the top 10 people but I have 2 people at 49 and 2 people at 46. In the attachment, I have a formula that pulls the top 10 people by their score, and it shows Jackie twice, and Berg twice.

How do I get it to show me Jackie and Jasmine both scored 49 and Berg and Michelle both scored 46?

Thank you for any help you can provide.

[FlameRetired]

Administrative Note:

Hello C.Yeti. Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[dosydos]

@orion please use this sumproduct formula in C2 and drag down

=SUMPRODUCT((A2=$T$2:$T$41)*(B2=$U$2:$U$41)*$V$2:$V$41)

[orion23]

That did it
Thank you very much dosydos. I really appreciate your help

[FlameRetired]

I get 0s past 'Black' with dosydos's.

and these all results with

Please Login or Register  to view this content.

or not so volatile

Please Login or Register  to view this content.

	A	B	C	D	E
1	ID	color	dd's	FF offset	FF I:sect
2	123456	blue	1	1	1
3	234567	green	10	10	10
4	345678	yellow	19	19	19
5	456789	white	28	28	28
6	567890	black	37	37	37
7		orange	0	38	38
8		purple	0	39	39
9		pink	0	40	40

[orion23]

Thank you flameretired...
I think the "0s" happen because there is no ID to match against so the result is "0", which is correct (for my needs)

Problem I now have is that dosydos' formula is that it works with numbers but not with text

[dosydos]

try this array formula in C2 and drag down.
Array formulas need to be entered with CTRL+SHIFT+ENTER. If done so correctly { } will be added around your formula.

=INDEX($V$2:$V$41,MATCH(A2&B2,$T$2:$T$41&$U$2:$U$41,0))

[FlameRetired]

Would an alternative summary layout be of help?

	123456	234567	345678	456789	567890
blue	1	9	17	25	33
green	2	10	18	26	34
yellow	3	11	19	27	35
white	4	12	20	28	36
black	5	13	21	29	37
orange	6	14	22	30	38
purple	7	15	23	31	39
pink	8	16	24	32	40

[orion23]

try this array formula in C2 and drag down.
Array formulas need to be entered with CTRL+SHIFT+ENTER. If done so correctly { } will be added around your formula.

=INDEX($V$2:$V$41,MATCH(A2&B2,$T$2:$T$41&$U$2:$U$41,0))

Thank you once again. Your solution has worked as intended (my mistake for using #'s with the sample spreadsheet)

[FlameRetired]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.