Index Match

[Simont485]

Hi all

So I have an issue with index match.

Column A is my index (A3:10) and Column B (B3:B10) is my match to C1 and result is in C2

Column B contains blank cells.

When C1 is blank I need to see a result in C2 from Column A

At present I get N/A

My Function in C2 is =Index(A3:A10,Match(C1,B1:B10))

 

[Simont485]

Nope....When C1 is blank I want D1 to return Person 1....... because B1 is blank.

 

[FormR]

Nope....When C1 is blank I want D1 to return Person 1....... because B1 is blank.

That's what the suggested formula in post 2 does for your example data?

Excel 2013/2016
	A	B	C	D
1	Person 1			Person 1
2	Person 2	NA
3	Person 3	H
Sheet1

Cell Formulas
Range	Formula
D1	=INDEX(A1:A3,MATCH(C1&"",INDEX(B1:B3&"",0),0))

 

[Simont485]

Ok so that now works..........??? Honestly it didn't earlier.Many many apologies and thanks

So if B3 was also Blank how would I get to see Person 3 in D2.......an so on for a longer list?

 

[FormR]

if B3 was also Blank how would I get to see Person 3 in D2.......an so on for a longer list?

Hi, you could try like this:

Excel 2013/2016
	A	B	C	D
1	Person 1			Person 1
2	Person 2	NA		Person 4
3	Person 3	H		Person 6
4	Person 4			Person 8
5	Person 5	H		Person 10
6	Person 6
7	Person 7	H
8	Person 8
9	Person 9	H
10	Person 10
Sheet1

Cell Formulas
Range	Formula
D1	=IFERROR(INDEX($A$1:$A$10,AGGREGATE(15,6,(ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))+1)/(LEN($B$1:$B$10)=0),ROWS(D$1:D1))),"")