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))

 

[FormR]

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

Hi, I think I understand - here is an option you can try:

=INDEX(A3:A10,MATCH(C1&"",INDEX(B3:B10&"",0),0))

 

[Simont485]

Thank you but I just get #value!.....

 

[FormR]

I just get #value!.....

Do you have any #VALUES in the range A3:B10?

 

[Simont485]

Yes......Column A range is staff names and column B range is activity.....

staff assigned an activity have a letter in column B and non assigned staff are blank. I need to see the staff names who are non assigned ie blank

 

[FormR]

Yes......

Yes you have #VALUE ! errors in the lookup range? I would first look to remove them, maybe by wrapping the formula that is causing them with the IFERROR() formula.

 

[Simont485]

Yes you have #VALUE ! errors in the lookup range? I would first look to remove them, maybe by wrapping the formula that is causing them with the IFERROR() formula.

Sorryms-understod what you meant....my lookup range has text only......

 

[FormR]

Perhaps you could post some example data that demonstrates what you are trying to do or demonstrates the #value ! error?

 

[Simont485]

A	B
Person 1
Person 2	NA
Person 3	H

<tbody>
</tbody>

This is an example of what I want to achieve.....

So cell c1 is data input and c2 will show Person 1 2 or 3

Index match will show Person 2 and Person 3 for the relevant letters in B but not Person 1 when C1 is blank

 

[FormR]

but not Person 1 when C1 is blank

So when C1 is blank you want the formula to return blanK?

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

Cell Formulas
Range	Formula
D1	=IF(C1="","",INDEX($A$1:$A$3,MATCH(C1,$B$1:$B$3,0)))