In Excel, how can I use the LEFT function inside of an INDEX/MATCH?

1
votes

So my problem is this:

Sheet 1 has a pivot table, Sheet 2 has the comparable data and formula, which will feed into a GETPIVOTDATA formula.

Sheet 1 entities (row names) looks like this: 123456_ABC

On Sheet 2 they look like this: 123456

I'm trying to use an index/match function to get the full entity names from Sheet 1 without having to insert a new column:

=INDEX('Sheet 1'!A:A, MATCH('Sheet 2'!A12, LEFT('Sheet 1'!A:A, FIND("_", 'Sheet 1'!A:A&"_")-1),0)

I'm following this guideline: 

=INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))

Its just not working. Does anyone have any suggestions?

EDIT: For some reason, SO is not showing the _ in between " " after FIND

1
excel-formulaexcel-2010
Did you try to test the functions individually? First check that FIND("_", 'Sheet 1'!A:A&"_") produces the expected result, then check that your LEFT(... works correctly, and so on. Also, post the result: what error message is displayed on the cell? - Mehdi
I don't have an Excel with me to check right now, byt are you sure about the A:A&"_" part inside your find function? FIND("_", 'Sheet 1'!A:A&"_") - Mehdi
As you doing string operations on whole column, it either won't work or will only work when entering formula in array type - once formula typed in instead of pressing enter, press ctrl+shift+enter - Raugmor
@mef Both functions work individually. I'm getting: #VALUE! I'm pretty sure that the problem is with the nested LEFT function, since it refers to an entire column instead of a cell. - triple_helix
@Raugmor Thanks, that did it. Now to figure out how to get this working correctly nested inside of my GETPIVOTDATA function. Thanks again! - triple_helix

1 Answers

0
votes

as requested by @pnuts

As you are doing string operations on whole column, it will only work when entering formula in array type - once formula typed in, instead of pressing enter, press ctrl+shift+enter