Desperately Need Index Solution

nancybrown

Well-known Member
My spreadsheet has two tabs: 2009Terms; 2008Terms. Each tabs have similar columns: A=Vendor ID; B=Inv#; C=Terms. I need to match A&B from both tabs and bring C=Terms from 2008 into the 2009 tab. This is what I'm using, but it returns #N/A for everything.

Column D added to concatenate A2,B2 on both tabs: =concatentate(a2,b2)

=INDEX(2008Terms!\$C\$2:\$C\$64000,MATCH(2009Terms!D2,2008Terms!\$D\$2:\$D\$64000,0))

Can any tell me what I am doing wrong please??

Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Not the most elegant.
But I would use a "key" column to match both and use vlookup.
Like below example.
For ease of reading i put both sheets in one.

Excel Workbook
ABCDEFGHIJ
1SHEET2008KeyVendorIDInvoiceTermsSHEET2009KeyVendorIDInvoiceTerms
212341211234121A12341211234121A
312351221235122B12351221235122B
412361231236123C12361231236123C
512371241237124D12371241237124D
612381251238125E12381251238125E
712391261239126F12391261239126F
812401271240127G12401271240127G
912411281241128H12411281241128H
1012421291242129I12421291242129I
Sheet3

It doesn't look like there is a problem with your current formula, unless there is some sort of formatting issue? Maybe try:

=index(2008Terms!\$C\$2:\$C\$64000,match(1,(2008Terms!\$A\$2:\$A\$64000=2009Terms!A2)*(2008Terms!\$B\$2:\$B\$64000=B2),0))

This needs to be confirmed with control+shift+enter and not just enter. I believe it will net the same results and you might want to make sure that the term you are looking up in 2009 is in fact on the 2008 sheet and exactly the same?

Hope that helps.

I'll give it a try. Thank you.

Replies
3
Views
324
Replies
5
Views
616
Replies
4
Views
606
Replies
1
Views
1K
Replies
1
Views
170

1,203,506
Messages
6,055,807
Members
444,826
Latest member
aggerdanny

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.

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

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