Match with Index and Match

[rex759]

Hello,
I’m unsure what formula will work correctly for this situation.

I thought I could use a combination of Match and index and match but it only works for the first column and can’t seem to get it to work for the rest.

Basically, if the number in cell F2 entered does not match a number in column A, move to column B. If still not a match, then move to column C.

For example, if 1240 is entered in cell F2, the result should be 1241.

Any help is appreciated.

Excel Workbook
	A	B	C	D	E	F	G
1	1111	1234	1235	8987		Enter a #	Result
2	1237	1239	1240	1241		1111	1234
3
4
5
Sheet1

Excel 2010

Cell Formulas
Range	Formula
G2	=IF(MATCH(F2,A:A,0),INDEX(B:B,MATCH(F2,A:A,0),0),IF(MATCH(F2,B:B,0),INDEX(C:C,MATCH(F2,B:B,0),0),IF(MATCH(F2,C:C,0),INDEX(D:D,MATCH(F2,C:C,0),0),"")))

 

[Matty]

There is probably a better way, but try:

=IFERROR(INDEX(A1:D2,SMALL(IF(A1:D2=F2,ROW(A1:A2)-ROW(A1)+1),1),SMALL(IF(A1:D2=F2,COLUMN(A1:D1)-COLUMN(A1)+1),1)+1),"")

Array entered, i.e. with CTRL+SHIFT+ENTER.

I've assumed that you'd just want a blank shown if the # entered was in Column D.

Matty

 

[T. Valko]

Hello,
I’m unsure what formula will work correctly for this situation.

I thought I could use a combination of Match and index and match but it only works for the first column and can’t seem to get it to work for the rest.

Basically, if the number in cell F2 entered does not match a number in column A, move to column B. If still not a match, then move to column C.

For example, if 1240 is entered in cell F2, the result should be 1241.

Any help is appreciated.

Excel Workbook
	A	B	C	D	E	F	G
1	1111	1234	1235	8987		Enter a #	Result
2	1237	1239	1240	1241		1111	1234
3
4
5
Sheet1

Excel 2010

Cell Formulas
Range	Formula
G2	=IF(MATCH(F2,A:A,0),INDEX(B:B,MATCH(F2,A:A,0),0),IF(MATCH(F2,B:B,0),INDEX(C:C,MATCH(F2,B:B,0),0),IF(MATCH(F2,C:C,0),INDEX(D:D,MATCH(F2,C:C,0),0),"")))

As long as the lookup value is unique, try this...

=SUMIF(A1:C2,F2,B1:D2)

 

[rex759]

Thank you both!

 

[T. Valko]

Thank you both!

You're welcome!

 

[Matty]

=SUMIF(A1:C2,F2,B1:D2)

That'd be the better way I was talking about then. Lol.

Matty