index and match not working right!

JW.Crofts

hello everyone,

for the life of me i cant get the index and match to give me the data i want to see and i know i have missed something simple.

my main sheet is "Master NSW Schedule" (MNS) i have a secondary sheet meant for other users "DETAILING SCHEDULE V2.0"(DSV2.0) this is to hide certain information from them but the issue is i need certain cells filled in by those users and sent back to the main sheet. I've tried linking cells but cant get my head around it so I've gone back to formula's. I tried VLOOKUP also and tried to adapt it from excel which gave me an unparseable error.

above are the 2 sheets with test data i have run 2 references both from the DSV2.0 one for the primary column "prostix no."(ps#) and "detail checked by"(DCB). where the formula is I need it to say "john" as it is in in DSV2.0

I'm hoping not to look stupid but I'm feeling that way right now. I would appreciate any and all help on this as its been a royal pain for the past 5 hrs.

Many Thanks

John.

Paul H

@JW.Crofts

If I am reading this right I think you are looking for this

= INDEX({DCB}, MATCH([Prostix No.]@row, {ps#}, 0)

Andrée Starå

Hi @JW.Crofts

I hope you're well and safe!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

JW.Crofts

@Andrée Starå

i have made 2 copies of these sheets and removed all data minus 1 line all the green cells are the ones that are supposed to go from DSV2.0 back to MNS

i also have a automation from MNS to DSV2.0 once the allocated check box is ticked this is how the sheet gets populated.

again thank you.

well its 10:34 and i need some sleep will pick this up in about 8 hrs when im at work.

Paul H

@JW.Crofts

If I am reading this right I think you are looking for this

= INDEX({DCB}, MATCH([Prostix No.]@row, {ps#}, 0)

SarahML

@JW.Crofts the way that I look at index match to help me remember the order of the formula is this:

=INDEX(column want to see, MATCH(cell to match on current sheet, column from source sheet to match, 0))

So for your formula for detailing checked by would be = INDEX({DCB}, MATCH([Prostix No.]@row, {Prostix No. column on MNS}, 0))

Let me know if you need me to explain the reasoning more.

JW.Crofts

@Paul H this worked a dream i knew i was missing something stupid serves me right for trying to do it late at night.

I would like to thank you all @Paul H , @SarahML and @Andrée Starå for the responses.

take care guys really appreciate it

Andrée Starå

@JW.Crofts

Happy to help!