asked on # tryed excell formula still not retrieving data

in regards to this question

https://www.experts-exchange.com/questions/29067900/Excel-formula-referencing-multiple-columns-between-tables.html

obviously i have more data then i included in the example, as sheet1 is 90k plus enterys.

and i have a couple other large sheets referenced that are not directly part of the problem

so the information i am trying to retrieve exists on column 3 (C) of sheet 4, i have modified the formulae as follows

should it matter that sheets 1-4 are from sql data connections? or is there something obvious im doing wrong

https://www.experts-exchange.com/questions/29067900/Excel-formula-referencing-multiple-columns-between-tables.html

obviously i have more data then i included in the example, as sheet1 is 90k plus enterys.

and i have a couple other large sheets referenced that are not directly part of the problem

so the information i am trying to retrieve exists on column 3 (C) of sheet 4, i have modified the formulae as follows

```
{=IFERROR(INDEX(Sheet4!A:O,MATCH(1,(Sheet4!A:A=B8)*(Sheet4!B:B=C8),0),3),"")}
```

and entered with ctrl + Shift + enter and i am getting no data. should it matter that sheets 1-4 are from sql data connections? or is there something obvious im doing wrong

Microsoft OfficeMicrosoft ExcelMicrosoft ApplicationsSpreadsheets

You should not reference entire columns with Array formulas (entered with Ctrl+Shift+Enter), because the formulas will calculate on the entire 1 million rows on the sheet, also when the data range is shorter.

If it is a table on Sheet4, refreshed from a sql connection, it is not needed.

Point to the data range (all rows below the header), and Excel inserts the field data name.

It will automatically adjust when new data is received.

If it is a sql import, not set up as a table, referencing from row 2 to the last row, will also update formulas to the new range when new data is imported.

And it does not need to be an array formula.

Something like this.

=IFERROR(INDEX(Data name column C,MATCH(1,INDEX((Data name column A=B8)*(Data name column B=C8),,),0),1),"")

With the data in the sample from the reference question, with 8 rows it will be

=IFERROR(INDEX(Sheet4!$C$2:CE$8,MATCH(1,INDEX((Sheet4!$A$2:$A$8=B2)*(Sheet4!$B$2:$B$8=C2),,),0),1),"")

Why it does not work is difficult to say without any data.

The formula looks ok.

Could you upload a sample.

If it is a table on Sheet4, refreshed from a sql connection, it is not needed.

Point to the data range (all rows below the header), and Excel inserts the field data name.

It will automatically adjust when new data is received.

If it is a sql import, not set up as a table, referencing from row 2 to the last row, will also update formulas to the new range when new data is imported.

And it does not need to be an array formula.

Something like this.

=IFERROR(INDEX(Data name column C,MATCH(1,INDEX((Data name column A=B8)*(Data name column B=C8),,),0),1),"")

With the data in the sample from the reference question, with 8 rows it will be

=IFERROR(INDEX(Sheet4!$C$2

Why it does not work is difficult to say without any data.

The formula looks ok.

Could you upload a sample.

Ejgil Hedegaard, for clarification the above equation only searches the top 8 rows of sheet4?

i have not tried since i cut the data down, all fields ar as they are meant to be. however all pertinent information has been changed.

Shums i tried the code in the file you included, it still did not work, hopefully the included file helps, see sheet5 I7. Im sure it is butchered from what you meant.

i have not tried since i cut the data down, all fields ar as they are meant to be. however all pertinent information has been changed.

Shums i tried the code in the file you included, it still did not work, hopefully the included file helps, see sheet5 I7. Im sure it is butchered from what you meant.

That is correct, the formula is made for 8 rows.

I don't see any sample data from you, so I used the workbook from Bill Prew in the previous question.

As mentioned you should use the real data range, then it will work for that, and automatically adjust when new data are imported.

It could also be the entire columns, but calculation will be slower when calculating on 900k not used rows, if your real data is 90k rows.

I don't see any sample data from you, so I used the workbook from Bill Prew in the previous question.

As mentioned you should use the real data range, then it will work for that, and automatically adjust when new data are imported.

It could also be the entire columns, but calculation will be slower when calculating on 900k not used rows, if your real data is 90k rows.

im sorry, i thought i uploaded it, i however only selected it.

test.xlsx

In attached file I made the connection on Sheet5 to the table on Sheet1, because both SignalID and AccountID exist on both sheets.

There are no matches to Sheet4.

Not sure that is what you need, but it shows the principle, and you can see it works.

Formulas in Column J on Sheet5.

The formula in J2 is

Then the formula in J2 is

csePixelated-test-1.xlsx

There are no matches to Sheet4.

Not sure that is what you need, but it shows the principle, and you can see it works.

Formulas in Column J on Sheet5.

The formula in J2 is

```
=IFERROR(INDEX(Table_cdiserv1_History1117_SignalHistory[OperCode],MATCH(1,INDEX((Table_cdiserv1_History1117_SignalHistory[SignalID]=A2)*(Table_cdiserv1_History1117_SignalHistory[AccountID]=B2),,),0),1),"")
```

On Sheet5 (2) is the same, but changed to at table.Then the formula in J2 is

```
=IFERROR(INDEX(Table_cdiserv1_History1117_SignalHistory[OperCode],MATCH(1,INDEX((Table_cdiserv1_History1117_SignalHistory[SignalID]=Tabel2[[#This Row],[SignalID]])*(Table_cdiserv1_History1117_SignalHistory[AccountID]=Tabel2[[#This Row],[AccountID]]),,),0),1),"")
```

sorry about the delayed reply, while the formula works it only gives me the opercode from sheet 1,

the best example in the data set is line 7 sheet1 [signalid] 611117 [accountid] 39880 [alarmzones] 99 should reference the [accountid] & [number] on sheet 4, to match up to [description] on sheet 4.

I realise i messed up on my sample data as sheet 4 has no data relative to the other sheets, so as an experiment i changed all lines with account number 39980 to 39880 this means that [accountid] 39880 [number] 99 = grumpy. so im trying to get the description.

the best example in the data set is line 7 sheet1 [signalid] 611117 [accountid] 39880 [alarmzones] 99 should reference the [accountid] & [number] on sheet 4, to match up to [description] on sheet 4.

I realise i messed up on my sample data as sheet 4 has no data relative to the other sheets, so as an experiment i changed all lines with account number 39980 to 39880 this means that [accountid] 39880 [number] 99 = grumpy. so im trying to get the description.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Try attached with Named Range.