CODEWITHSUNDEEP
excel
Roxanne Allatt
Roxanne Allatt

Reputation: 5

Excel - Index Match using the Left property

newbie here!
After scouring the web, can't quite find an exact answer:

I have two tabs in an excel sheet, one with master of information (PARAM), one is a template that gets copied (TEMPLATE). I need to index items from column A:A of Master tab if the two first characters are equal to $E$6 on the Template tab. The results go to Template tab A14 and downwards.

Not working, I'm assuming because I'm referencing the same column multiple times... I'm not catching something in the logic behind what I need in my formula.

Current formula :

=IFERROR(INDEX(PARAM!A:A, MATCH(IF(LEFT($E$6, 2)=LEFT(PARAM!A:A, 2), PARAM!A:A))), "")

Thank you

Upvotes: 0

Views: 100

Answers (2)

user4039065
user4039065

Reputation:

Try,

=iferror(index(param!a:a, aggregate(15, 6, row($1:$999)/(left($e$6, 2)=left(param!a$1:a$999, 2)), row(1:1))), text(,))

Fill down for 1101, 1102, etc. The remaining information can now be retrieved with basic VLOOKUP.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152495

Match allows the use of WildCards:

=IFERROR(INDEX(PARAM!A:A, MATCH(LEFT($E$6, 2)& "*", PARAM!A:A,0)), "")

Upvotes: 0

Related Questions