index/match simple question

[and8eye]

i mean, in sheet1 i have this:

in sheet2:

in sheet3:

my question is:

how can i use index/match across multiple sheets (even when new sheet is created, possible?) at sheet1 A1 (i have thousand lines in B column in reality)?

thank you all

 

[Dryver14]

what formula are you using now? most formulas will stay across multiple sheets if additional sheets are added inbetween so you could make the last sheet not count IE nil return or zeros then insert new sheets just in front of that one

 

[and8eye]

what formula are you using now? most formulas will stay across multiple sheets if additional sheets are added inbetween so you could make the last sheet not count IE nil return or zeros then insert new sheets just in front of that one

im using index/match now, but i have to put name of sheet into the formula, im thinking of indirect, but i dont know how

 

[and8eye]

please

 

[Dryver14]

I do not know a way to use indirect with this function, how many sheets are we talking for a few you could do something like this

iferror(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0)))

Other than that I can offer no more, sorry, sometimes saying simple question doth not a simple answer return me thinks

 

[and8eye]

I do not know a way to use indirect with this function, how many sheets are we talking for a few you could do something like this

iferror(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0)))

Other than that I can offer no more, sorry, sometimes saying simple question doth not a simple answer return me thinks

i have somes but this way when new sheet is created, i will have to update the formula again

or any other formula do you think its possible?

thanks anyway tho

 

[Dryver14]

I dont think there is any formula that knows a new sheet has been added and to update itself.

There may be a vba solution but I would not know how to do it sorry.

Good luck

 

[and8eye]

I dont think there is any formula that knows a new sheet has been added and to update itself.

There may be a vba solution but I would not know how to do it sorry.

Good luck

thank you

btw, can you please take a look at this: https://www.mrexcel.com/forum/excel-questions/1047836-vlookup-question-3-a.html

 

[and8eye]

please

 

[and8eye]

someone please