Index Match - REF error but formula looks correct?

0
votes

I've got two sheets and I'm trying to use Index Match to fill a summary table.

It looks up the name in summary table and fills a number of columns. I've checked the formula but it's giving me a REF error (note- I've counted the rows and columns and these are correct) - using the excel function tool it also shows its picking up the correct row.

My Formula :

=INDEX('Deal Return Detail'!AA5:AQ24,MATCH('Portfolio Summary - Graphs'!B27,'Deal Return Detail'!F:F,0),MATCH(G26,'Deal Return Detail'!2:2,0))

Deal return Detail is the sheet with values Portfolio summary is the summary sheet

Welcome any help as I've checked on here and a number of index match tutorials but no luck as it seems to be a correct formula.

thanks,

L

1
excelindexingmatch
Check each part individually. What does MATCH('Portfolio Summary - Graphs'!B27,'Deal Return Detail'!F:F,0) return? What does MATCH(G26,'Deal Return Detail'!2:2,0) return? – Foxfire And Burns And Burns
Your ranges don't tie up. You're getting a row position from an entire column and a column position from an entire row, and then applying them to a smaller range. – Rory
Hi Rory - no idea what you mean on ranges don't tie up? Can you explain? I've done each part and they seem to be returning the correct rows and columns, but when together they REF out. – SuperLoz
As I said, what are the individuals values you are getting of each match where it fails? – Foxfire And Burns And Burns

1 Answers

0
votes

Disclaimer : I'm just expanding @Rory comments since the OP didn't get the gist..

In index(a,b,c) function. the dimension of 'a' should match the row height of 'b' and the column width of 'c'.

So let sat 'a' is a 20 column by 50 rows data/range, the 'b' should be a 50 rows match/list and 'c' should be a 20 rows match/list.

in your case.. something like :

=INDEX('Deal Return Detail'!AA5:AQ24,MATCH(G26,'Deal Return Detail'!A2:Q2,0),MATCH('Portfolio Summary - Graphs'!B27,'Deal Return Detail'!F5:F24,0))

should do. Hope it explains.. ( :