Index and Match syntax error

**kocumna** · 06-01-2018, 07:39 PM

Good afternoon,

I have two attachments. I am trying to find the bill no. from the Weekly HMO ss on the HMO Traditional ss and once I have, put the savings amount in the net savings column in the traditional ss.

This is the formula I have, but it's not working.

=IFERROR(INDEX('[Weekly HMO 052518.xlsm]Week Ending 052518'!$O:$O,MATCH(LEFT(W123,7),LEFT('[Weekly HMO 052518.xlsm]Week Ending 052518'!$R:$R,7),)),"")

Thank you for your help once again!

**jeffreybrown** · 06-01-2018, 08:18 PM

Based on your description, is this what you seek?

=IFERROR(INDEX('[Sample Weekly HMO 052518.xlsm]Week Ending 052518 '!$G:$G,MATCH($O5,'[Sample Weekly HMO 052518.xlsm]Week Ending 052518 '!$J:$J,0)),"")

I'm not sure what the whole Left is doing.

**kocumna** · 06-01-2018, 08:34 PM

Thank you so much Jeff, it worked. I see you used $05 instead of $O$O to search the entire column. Would you please explain that a little to me?

**jeffreybrown** · 06-01-2018, 09:45 PM

You are very welcome. Glad it worked out for you.

If you look at the three arguments that make-up the Match function, the first argument looks at a single lookup value and then the second argument matches the lookup value against the lookup array.

=MATCH(lookup_value,lookup_array,match_type)

See if this reference helps...

http://www.contextures.com/xlFunctions03.html

If that takes care of your original query, please don't' forget to marked this thread as solved.

**kocumna** · 06-14-2018, 02:07 PM

Is there a way that I can edit this formula so that I can reuse it for a different workbook and worksheet without having to change the formula every time?

**jeffreybrown** · 06-14-2018, 02:20 PM

If you copy the formula as in post #2 it will transfer as is and you will have to update to the appropriate references. It's possible something can be done to keep the editing down, but not sure what all you are doing.

**kocumna** · 06-14-2018, 02:32 PM

Could the ActiveSheet function work and if so, would you please tell me how you would go about that?

I am still trying to do what my original posts says, but my workbook and worksheets change each week.

"I have two attachments. I am trying to find the bill no. from the Weekly HMO ss on the HMO Traditional ss and once I have, put the savings amount in the net savings column in the traditional ss."

**jeffreybrown** · 06-14-2018, 02:49 PM

Originally Posted by kocumna

Could the ActiveSheet function work and if so, would you please tell me how you would go about that?

I don't know of an ActiveSheet function in a formula, only in VBA and that doesn't seem what you are after. I'm going to ask for some other's to like in on this and offer any other suggestions.

**CK76** · 06-14-2018, 03:06 PM

I'd just use Edit links. Found under Data ->Queries&Connections

And use "Change Source" to change source in one shot for entire reference to another workbook.
0.JPG

Probably the easiest and likely the fastest to mass change external workbook reference.

Index and Match syntax error

LinkBack

Thread Tools

Rate This Thread

Display

Index and Match syntax error

Re: Index and Match

Re: Index and Match

Re: Index and Match syntax error

Re: Index and Match syntax error

Re: Index and Match syntax error

Re: Index and Match syntax error

Re: Index and Match syntax error

Re: Index and Match syntax error

Thread Information

Users Browsing this Thread

Similar Threads

INDEX MATCH syntax..

[SOLVED] INDEX/MATCH syntax

[SOLVED] Formula Syntax with INDEX/MATCH and other table references

[SOLVED] INDEX/MATCH/MIN(>1) Syntax Difficulty

[SOLVED] Index Match Match - syntax problem

VBA syntax for Index/Match function

[SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings

Bookmarks

Bookmarks

Posting Permissions