Index and Match help

[SCG]

Hi once again.
I have been using Index and Match quite happily for a while now however I now want to be able to use Index and Match on two different spreadsheets.

So for example I want to find the number for Mr B (result Col A Sheet 2). I put my formula in Sheet 1 and go to Sheet 2 where my data is and run the Index and Match. What I want to do is if it doesn't find it in Sheet 2, make the forumla then look at a simliar table in Sheet 3 to find the result.

What do you think?


Col A Col B
123 Mr A
1234 Mr B
12345 Mr C
123456 Mr D
1234567 Mr E

Thanks again

 

[WinteE]

If(index_match_sheet2="";index_match_sheet3;index_match_sheet2)

 

[SCG]

If(index_match_sheet2="";index_match_sheet3;index_match_sheet2)

Hi WinteE,

Any chance you can help with creating the formula for me?

Thanks

 

[T. Valko]

Hi once again.
I have been using Index and Match quite happily for a while now however I now want to be able to use Index and Match on two different spreadsheets.

So for example I want to find the number for Mr B (result Col A Sheet 2). I put my formula in Sheet 1 and go to Sheet 2 where my data is and run the Index and Match. What I want to do is if it doesn't find it in Sheet 2, make the forumla then look at a simliar table in Sheet 3 to find the result.

What do you think?


Col A Col B
123 Mr A
1234 Mr B
12345 Mr C
123456 Mr D
1234567 Mr E

Thanks again

What version of Excel are you using?

 

[SCG]

Hi T. Valko

Using 2007 here

 

[T. Valko]

Hi T. Valko

Using 2007 here

Try something like this...

=IFERROR(INDEX(Sheet2!A2:A6,MATCH(A2,Sheet2!B2:B6,0)),IFERROR(INDEX(Sheet3!A2:A6,MATCH(A2,Sheet3!B2:B6,0)),""))

 

[SCG]

Try something like this...

=IFERROR(INDEX(Sheet2!A2:A6,MATCH(A2,Sheet2!B2:B6,0)),IFERROR(INDEX(Sheet3!A2:A6,MATCH(A2,Sheet3!B2:B6,0)),""))

T. Valko thats great. Thanks for this. have a good day

 

[T. Valko]

T. Valko thats great. Thanks for this. have a good day

You're welcome. Thanks for the feedback!