Index and match formula between 2 workkbooks not giving me data

Hello I have a formula for index match it works for the first 6 columns and not for the 7th and 8th

=IFERROR(INDEX('[ServiceIn.xlsm]Customer Details'!$B$3:$G$1048576,MATCH(B3,'[ServiceIn.xlsm]Customer Details'!$B$3:$B$31000),7),"")

If I change the 7 at the end back to a 6 I get the previous column but it has an error when the formula has the 7 or 8 column

error is #REF there is data in the location

This formula works

=IFERROR(INDEX('[ServiceIn.xlsm]Customer Details'!$B$3:$G$1048576,MATCH(B3,'[ServiceIn.xlsm]Customer Details'!$B$3:$B$31000),6),"")

7 as the last MATCH argument would be looking at column H and 8 would be column I. As the range in your INDEX covers only B:G (6 columns), anything higher than 6 will not work.

Beth.

Ah yeah I forgot about that I did that before adding the other columns thanks for your help.

Glenn not sure what you mean about electing the column in index do u have an example to explain it thanks.

An example of what Glenn means is exactly what Ali proposed in post #2.

You see the INDEX is only looking at column G rather than B:G? That means the result can only come from G and is what Glenn was suggesting.

If you need it to be more dynamic than that then you could possible use an INDEX/MATCH/MATCH to pick the row and column, but without seeing a sample file and having more of an idea of what you're trying to achieve it's difficult to say if it would work for you.

Beth.

Ah thank you Beth I understand now