# Thread: Index Match Thanks: 0 Likes: 0

1. ## Index Match

I feel like my brain just isn't working correctly, but here is what I am trying to do.

I have 2 tables, I need to add a column to the first table, and then do a lookup for the "ITEM ID" from the second table and return the "VERIFY" number to the NEW column in the first table.

I am pretty sure this is a decently simple Index match formula, but for the life of me I am not getting it to work...

First Table: (1 Column)
 Folder ID 04.315.201 6/11/2019 PO 1 04.315.201 6/11/2019 PO 2 04.315.202 6/11/2019 PO 1 04.315.202 6/11/2019 PO 2 04.315.311 6/11/2019 PO 1 04.315.311 6/11/2019 PO 2
 Second Table: (2 Columns)
 ITEM ID VERIFY 04.315.201 1 04.315.202 2 04.315.311 3

Thanks for any help!
-Ben

3. ## Re: Index Match Help

Originally Posted by mole999
What I am currently working with is the following type. But obviously in my sheet my tables are named (Second Tables = Tbl_Item)
=INDEX(tbl_Item,MATCH(tbl_Item[Item ID],[Folder ID],0),2)

4. ## Re: Index Match Help

Maybe...

 A B C D E F 1 Folder ID Verify ITEM ID VERIFY 2 04.315.201 6/11/2019 PO 1 1 04.315.201 1 3 04.315.201 6/11/2019 PO 2 1 04.315.202 2 4 04.315.202 6/11/2019 PO 1 2 04.315.311 3 5 04.315.202 6/11/2019 PO 2 2 6 04.315.311 6/11/2019 PO 1 3 7 04.315.311 6/11/2019 PO 2 3 8

Formula in B2
=INDEX(tbl_Item[VERIFY],MATCH(LEFT([@[Folder ID]],10),tbl_Item[ITEM ID],0))

Hope this helps

M.

5. ## Re: Index Match Help

Originally Posted by Marcelo Branco
Maybe...

 A B C D E F 1 Folder ID Verify ITEM ID VERIFY 2 04.315.201 6/11/2019 PO 1 1 04.315.201 1 3 04.315.201 6/11/2019 PO 2 1 04.315.202 2 4 04.315.202 6/11/2019 PO 1 2 04.315.311 3 5 04.315.202 6/11/2019 PO 2 2 6 04.315.311 6/11/2019 PO 1 3 7 04.315.311 6/11/2019 PO 2 3 8

Formula in B2
=INDEX(tbl_Item[VERIFY],MATCH(LEFT([@[Folder ID]],10),tbl_Item[ITEM ID],0))

Hope this helps

M.
I was thinking that originally, but the issue is that the LEFT portion before the date changes since they are based on a totally random item number, that first number string could vary from like 7-14 characters i believe (Another Example: AC-3010 4/26/2019 PO 1 & HCS-010-25-22L 6/28/2019 PO 1)

6. ## Re: Index Match Help

is the first string part always without spaces?

7. ## Re: Index Match Help

If there are no spaces in the number and the first space only occurs between this number and date then @Marcelo Branco's formula can be changed to:

Code:
`=INDEX(tbl_Item[VERIFY],MATCH(LEFT([@[Folder ID]],SEARCH(" ",[@[Folder ID]])-1),tbl_Item[ITEM ID],0))`

8. ## Re: Index Match Help

Originally Posted by mole999
is the first string part always without spaces?
If you are referring to the Item ID, yes. The Item ID does not contain any spaces.

Also if you really wanted to eliminate the spaces you could utilize a TRIM formula(If it was needed).

9. ## Re: Index Match Help

you need to search for the space, as Aryatect said above

10. ## Re: Index Match Help

Try

B2
=INDEX(tbl_Item[VERIFY],MATCH(1,INDEX(--ISNUMBER(SEARCH(tbl_Item[ITEM ID],[@[Folder ID]])),),0))

M.