Using Index Match to match against more than one column, but only reference one row

You can mark thread unsolved and ask question

Basic rule is: one problem - one thread

Late comer / afterthought.

Array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E
1	Lookup	Result	Lookup	Table	Result Table
2	8	red	1	6	blue
3			2	7	green
4			3	8	red
5			4	9	pink
6			5	10	black

Yup! I started to type, "We get paid?" LOL Good catch! Got in a hurry ... weekend.

Here it is.

Formula:

Please Login or Register  to view this content.

It returns an array of 1(s) and #DIV/0 errors. MATCH ignores errors in its second argument and returns the row number of the first 1 it finds.

I do that partly out of habit. MATCH(1,--((A2=$C$2:$C$6)+(A2=$D$2:$D$6)),0) (array of 1s and 0s) often works as well, but I find that occasionally I get #N/A returns with that.

It happens rarely and usually when the operation is * and not +. Strangely when it does happen the 1 in the array to be matched is always in the 1st row of the array. I have never understood this, but reciprocating the array remedies it.

The main thing is that you are trying to match the first 1 in the array.

Does this help?

Or this

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E
1	Lookup	Result	Lookup	Table	Result Table
2	8	red	1	6	blue
3	7	green	2	7	green
4	10	black	3	8	red
5	6	blue	4	9	pink
6	9	pink	5	10	black

Yes. I forget about good old reliable LOOKUP. Fewer function calls.