Index Match

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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,362
Office Version
2019, 2016, 2013
Platform
Windows
Re: Index Match Help

what is your current formula
 

Ben85

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

Ben85

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

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,362
Office Version
2019, 2016, 2013
Platform
Windows
Re: Index Match Help

is the first string part always without spaces?
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
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))
 

Ben85

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

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,362
Office Version
2019, 2016, 2013
Platform
Windows
Re: Index Match Help

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Re: Index Match Help

Try

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

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,050
Messages
5,484,411
Members
407,438
Latest member
DKrakken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top