Nested IF/THEN with INDEX MATCH and return LEFT value

**Aquarock** · 04-17-2018, 11:32 AM

Okay I can only get this to partially work (cell B5 on attached doc) and I've tried to modify it like in cell B6 but its not returning anything.
I only want the LEFT three characters to return based on the data entered in B1, and B2 then looked up against the Table in Columns H, I, J.

Any help is greatly appreciated!

**JohnTopley** · 04-17-2018, 11:37 AM

I Should read requirement!!

**63falcondude** · 04-17-2018, 11:39 AM

Try this in B5:

=IFERROR(SUBSTITUTE(INDEX(J$2:J$19,INDEX(MATCH(B$1&"_"&B$2&A5,H$2:H$19&I$2:I$19,0),0))," A","")+0,"")

**Special-K** · 04-17-2018, 11:41 AM

in B5
=IFERROR(LEFT(INDEX($J$2:$J$19,SMALL(IF((B$1&"_"&B$2=$H$2:$H$19),ROW($A$2:$A$19)),ROW(A1))-(ROW(A$2)-1),1),3),"")
Array formula, use Ctrl-Shift-Enter
and copy down to B10

**JohnTopley** · 04-17-2018, 11:46 AM

Or

=IFERROR(INDEX(LEFT($J2:$J100,3),,MATCH($B$1&"_"&$B$2&$A5,H2:H100&I2:I100,0)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**Aquarock** · 04-17-2018, 11:52 AM

you all rock.....i now see the error in my ways! see what i did there.

your help is so greatly appreciated everyone!

**63falcondude** · 04-17-2018, 11:56 AM

Glad we could help. Thanks for the rep!

Nested IF/THEN with INDEX MATCH and return LEFT value

LinkBack

Thread Tools

Rate This Thread

Display

Nested IF/THEN with INDEX MATCH and return LEFT value

Re: Nested IF/THEN with INDEX MATCH and return LEFT value

Re: Nested IF/THEN with INDEX MATCH and return LEFT value

Re: Nested IF/THEN with INDEX MATCH and return LEFT value

Re: Nested IF/THEN with INDEX MATCH and return LEFT value

Re: Nested IF/THEN with INDEX MATCH and return LEFT value

Re: Nested IF/THEN with INDEX MATCH and return LEFT value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Using LEFT inside INDEX-MATCH to find values that match first 5 letters of referenced cell

[SOLVED] Index / Match - match 3 input values and return the results from the index

Using LEFT, MID, or RIGHT within VLOOKUP or INDEX/MATCH/MATCH

[SOLVED] INDEX/MATCH? - Return Value to left with IF function

[SOLVED] Index + Match to return result with Left function

Return the value of a cell/multiple criteria using nested INDEX, MATCH, IF array formula

Nested Match(index()) vs. Match() array formula.

Bookmarks

Bookmarks

Posting Permissions