Formula using INDEX and MATCH

[Robert_Conklin]

I am using the following formula in a spreadsheet to autofill cells with data from another sheet and it works.

=INDEX(Table15[Global ID],MATCH([@Name],Table15[Name],0))

I am using the same formula in another workbook and I keep getting a syntax error.

=INDEX(Table15[SAP DESCRIPTION],MATCH([@SAP #],Table19[SAP DESCRIPTION],0))

Why is the new formula not working?

 

[galbert]

Hi,

Are the 2 ranges you are using the same size? The one in Table15 and the one in Table19? Different sized ranges are a common mistake.

Regards,
Gabor

 

[Robert_Conklin]

They are, but the first formula (table15) is in a completely different workbook.

 

[galbert]

There's a good chance Excel doesn't like the # in the formula. Try this:
=INDEX(Table15[SAP DESCRIPTION],MATCH([@[SAP '#]],Table19[SAP DESCRIPTION],0))

I usually start typing the table references and use Tab to autocomplete based on what Excel offers.

 

[Robert_Conklin]

I have and I receive the below error message:

 

[galbert]

That's weird. I have just tried to replicate your table structure (as best as I could, based on your formula) and tried it and it worked for me. What happens if you delete the [@[SAP '#]] argument and start typing [, then select @this row from the dropdown list offered in your formula bar, then continue typing SAP and select the SAP # from the dropdown list again to let Excel auto-complete the reference for you, then you add an additional ] manually? Following these steps, I get [@[SAP '#]], which the formula editor accepts without any error messages

 

[Robert_Conklin]

I chose the suggestions that Excel offered and completed the formula. It looks exactly as above and returns an N/A value.

I checked the other sheet in the workbook and that SAP number is there. So it should have brought the SAP description over for that SAP #. I am not sure what is going on. I use the same formula in two other workbooks and they work like a champ.

 

[galbert]

It just occurred to me that you are looking up the SAP number in the SAP description column in Table 19, hence the N/A error. Try referring to a column containing SAP# in Table19.

 

[Robert_Conklin]

I want to match the SAP numbers and bring back the SAP Description. Is the formula I used wrong?

 

[galbert]

The 2nd argument inyour MATCH is wrong, then. Your formula should be something like this (assuming the column containing the numbers in Table19 is actually called SAP #):

=INDEX(Table15[SAP DESCRIPTION],MATCH([@[SAP '#]],Table19[SAP '#],0))

When you use the combination of INDEX and MATCH to accomplish such lookups you should always think of it as the range in the INDEX is your range you want to return values from, the 1st argument of MATCH is the lookup value and the 2nd argument of MATCH is the lookup range (so the lookup value must be found in the lookup range).

Hope it helps.