Ben85

New Member
Joined
Jan 9, 2017
Messages
30
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

<tbody>
</tbody>


Second Table: (2 Columns)

<colgroup><col></colgroup><tbody>
</tbody>
ITEM IDVERIFY
04.315.2011
04.315.2022
04.315.3113

<tbody>
</tbody>


Thanks for any help!
-Ben
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Re: Index Match Help

what is your current formula
 
Upvote 0
Re: Index Match Help

what is your current formula

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)
 
Upvote 0
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.
 
Upvote 0
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

<tbody>
</tbody>


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)
 
Upvote 0
Re: Index Match Help

is the first string part always without spaces?
 
Upvote 0
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))
 
Upvote 0
Re: Index Match Help

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).
 
Upvote 0
Re: Index Match Help

you need to search for the space, as Aryatect said above
 
Upvote 0
Re: Index Match Help

Try

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

M.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top