Index Match using two sheets

Marlene York

 Hi!

I am trying to use Index Match but not having a clear understanding of what it is I need to do is making it difficult. All I need to do is when I add the Reference Number, the formula should search sheet A and sheet B for the number and pull 4 columns of data based on this number.

What I would like to be able to do (please note that for confidentiality reasons I have changed the column heading titles):

·        Column – Reference number – this number is a unique identifier.

o  The formula will search the two sheets for the unique identifier. The reason for the two sheets is that in total there are 34,600 rows, according to the information I was able to find the most rows permitted per sheet is 20,000.

o  The reference number will be manually entered into the ‘working sheet’.

·        The formula will then retrieve the information for the next 4 columns from one of the two sheets. This is the formula that was suggested (note, I have to change the details to match my columns which is where I am having difficulty) =IFERROR(INDEX({Project numbers project name}, MATCH([Project ID]@row, {Sht A project ID}, 0)), INDEX({sht B project name}, MATCH([Project ID]@row, {sht B Project ID}, 0)))

o  The 4 column headings are:

1.      Column LO;

2.      Column LG;

3.      Column E;

4.      Column RA.

I’ve tried changing what I think I need to change but keep receiving an error message of #UNPARSEABLE.

Genevieve P.

Hi @Marlene York

The structure of this formula is correct! I've just tested it across 2 sheets:

=IFERROR(INDEX({Project numbers project name}, MATCH([Project ID]@row, {Sht A project ID}, 0)), INDEX({sht B project name}, MATCH([Project ID]@row, {sht B Project ID}, 0)))

#UNPARSEABLE means the formula is unable to find what you're referencing. You'll need to ensure that the column in your sheet where you're writing the formula is titled Project ID or if it's not, swap that out for your column name. So, if it's "Reference Number", then like so:

=IFERROR(INDEX({Project numbers project name}, MATCH([Reference Number]@row, {Sht A project ID}, 0)), INDEX({sht B project name}, MATCH([Reference Number]@row, {sht B Project ID}, 0)))

If you can confirm that the column name in this current sheet is the same as what's in your formula, the next thing to check is the Cross Sheet References.

When you're building out a formula based on a suggestion, you'll need to delete out each {reference} and add in your own personal cross-sheet reference from scratch. (See: Formulas: Reference Data from Other Sheets)

If neither of this has helped, can you provide a screen capture of your formula open in your sheet, but block out sensitive data? We could also test each individual INDEX(MATCH to see which one is throwing the error.

Cheers,

Genevieve

Marlene York

Hi Genevieve,

Thank you so much for taking the time to respond. I must be doing something wrong and I cannot work it out. These are my column headings on the sheet I would like the information to auto populate:

The other two sheets have the following headings:

=IFERROR(INDEX({LO}, MATCH([Reference Number]@row, {{Reference Number - Sheet A Range 3}}, 0)), INDEX({{Reference Number - Sheet B Range 1}}, MATCH([Reference Number]@row, {{Reference Number - Sheet B Range 3}}, 0)))

Once again thank you for your help.

Regards,

Marlene

Genevieve P.

Hi @Marlene York

In this current formula, I can see that there are double curly brackets: {{

Cross-sheet references only want one {

Try removing out the extras:

=IFERROR(INDEX({LO}, MATCH([Reference Number]@row, {Reference Number - Sheet A Range 3}, 0)), INDEX({Reference Number - Sheet B Range 1}, MATCH([Reference Number]@row, {Reference Number - Sheet B Range 3}, 0)))

Did that work for you? If not, can you show each {reference}? Click in the middle of the reference, select edit reference, then show the pop-up window that appears to find what column it's looking at.

Thanks!

Genevieve