Using INDEX MATCH with results of a formula

[John McPhee]

My formula is =INDEX(C9:C12,MATCH(B16,B9:B12,0))
where:
C9:C12 are numbers that are paired with a non-alphabetical list of words in B9:B12.
B16 is the first cell in a column in which the words in the B9:B12 list appear as the result of a formula which picks them up from another sheeet.

If I type the words into B16 and the cells below, the formula works.
If I type the words into the formula instead of B16, the formula works.
But when the formula is looking for the word in col B that appears as the result of the formula, I get #N/A.

Any clues?

 

[mrshl9898]

Get the formula to produce the word then use =EXACT(cell with result, cell with word in column B) to see if they match.

Maybe add trim after to see if there's a different result. =EXACT(TRIM(first cell),TRIM(second cell))

If they fail, copy the result as a value, and paste the 2 words here from the formula bar in quotation marks. "word 1" "word 2"

 

[John McPhee]

Thanks. Did that both of those things and everything returns TRUE. Is it the case that my reference to B16 is registering a formula and the words in B9:B12 are entered as text? And if that is the case, is there a way around that?

 

[mrshl9898]

I wouldn't imagine that as a problem, looks like it is though. You could try =INDEX(C9:C12,MATCH(TEXT(yourformula,"####"),B9:B12,0))

Failing that, can you paste in the table?

 

[John McPhee]

Can you just confirm the yourformula,"####" bit? By yourformula, do you mean the cell reference that contains the formula, or the actual content of the formula? And what is "####"? I have tried posting a screen shot of the issue, but I'm afraid that I can't get any of the paste functions to work - not for lack of trying, but probably lack of understanding.

 

[mrshl9898]

Sure, TEXT(yourformula,"####") turns something into a text value.

I'm assuming you have a formula in B16 that produces the value you want to search for? Use that formula in there.

 

[John McPhee]

Thanks. Tried that. Still no luck. I'm at a loss. And the only pasting option I can get to work is Paste as plain text, which is not very helpful in trying to show what I've got, but here it is anyway. - just imagine all these things are in columns.

Initial 100
Development 200
Mid 300
Late 300


Day Growth stage Root depth
2-Jan Initial #N/A
3-Jan Initial #N/A

The words Initial - Late in the top list are B9:B12, and the numbers to the right are C9:C12. Those are all manually populated cells. The two words Initial to the right of the dates are B16:17 and are the start of a long column that picks those words up from another spreadsheet via the formula =VLOOKUP(A16,'Est daily crop requirements'!$B$14:$H$164,2). The formula to the right of that (the one that returns #N/A) is =INDEX($C$9:$C$12,MATCH(B16,$B$9:$B$12,0)), which I was hoping would pick up the appropriate number from C9:C12 matched against the appropriate word in B9:B12.

Thanks for your patience.

 

[mrshl9898]

So when you type Initial in B16, C16 = 100, but with the lookup it is N/A? and with the lookup =EXACT(B16,B9) is TRUE?

I have replicated this and it's all working fine for me, with the VLOOKUP returning Initial in B16 and the MATCH using =INDEX($C$9:$C$12,MATCH(B16,$B$9:$B$12,0))

 

[John McPhee]

Your understanding is correct. If I type the word Initial into B16, C16=100, and if I replace B16 in the formula in C16 with the word "Initial", C16 = 100, but with the VLOOKUP formula in B16, and B16 in the formula in C16 = #N/A.

But here's the weird bit - I just tried EXACT(B16,B9) again and it now returns FALSE, whereas before it returned TRUE. So I tried EXACT with TRIM and it returns TRUE. So that suggests to me there are some spare spaces floating around in either B9 or B16. I've been into the formula bar and deleted any spaces that might exist before or after the text and the formula (not that you can see them) and it makes no difference to anything. And I don't understand how you have replicated the exact same formula and it works, but mine doesn't.

 

[mrshl9898]

I think those spaces maybe floating around 'Est daily crop requirements'! column C. Try trim on the column and repaste in the trimmed data.