Index/match:can any one help

[andynewns]

Hello,
New to the forum as i am in desparate need of some help.

This may be quite hard to explain

my formula is - and this is the exact code

=INDEX(SalesPipelineDetails,MATCH(1,IF('Sales Performance (Top 5)'!C8 = 'Sales Pipeline Details'!C:C,'Sales Pipeline Details'!AO:AO,0),0),4)


this returns an N/A#
SalesPipelinedetails is a named range

for the row - MATCH(1,IF('Sales Performance (Top 5)'!C8 = 'Sales Pipeline Details'!C:C,'Sales Pipeline Details'!AO:AO,0),0) will return row 6500,

and then column is 4

If i highlight the formular in the formula bar and press f9 it returns the correct answer, however in the cell it returns N/a

can any one help

Thanks

Andy

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[ConneXionLost]

Your IF function is comparing a single cell (C8) to a range (C:C). If you're getting anything (other than FALSE) it's likely a coincidental error due to being on the same row number as the desired result from C8.

[NBVC]

Also, if you are in XL2003, you can't use whole column references in this formula and if you are in post XL2003, you should not use whole column references.

Also, the named range must be the same size as the ranges (height-wise) in the Match part.

Try:

Please Login or Register  to view this content.

where 'Sales Pipeline Details'!C$1:C$1000 is the same size as the SalesPipelineDetails range height-wise.