Return a yes, no value from another sheet using If Index Match formula

Question

I have a master sheet which pulls data from another workbook.

the workbook is called example.xlsx. This workbook has multiple sheets inside of it.

In this Master sheet I have created the formula below;

=IF(INDEX('[example.xlsx]sheet1'!F:F>0,"Yes","No"),MATCH(B8,'[example.xlsx]sheet1'!A:A,0))

I get a #VALUE error.

B8 = master sheet = name of a person F:F = example.xlsx = is a column of numbers ranging from 0 - 9+ A:A = example.xlsx = a list of names.

=================================

original formula before i used IF

=INDEX('[example.xlsx]sheet1'!F:F,MATCH(B8,'[example.xlsx]sheet1'!A:A,0))

=================================

Can anyone point me in the right direction please so that instead of 29, it will show a Yes or No based on t is greater than a number(5)?

Thanks for that. I know I am using it incorrectly, I am asking for some help and assistance on where I am going wrong and how I could rectify it? — SSP, Jul 12 '18 at 9:40
In this Master sheet I have created the formula below You build it using formula builder or input it manually? Try to build it step-by-step using additional columns, and, when success, compile everything back into one complex formula. — Akina, Jul 12 '18 at 9:48

PeterH · Accepted Answer · 2018-07-12 09:51:50Z

0

Try the below, let me know if this works for you:

=IF(INDEX([example.xlsx]sheet1!F:F,MATCH(B8,[example.xlsx]sheet1!A:A,0),1)>0,"yes","no")

This will return a Yes in place of any number returned from your original INDEX formula that is greater than 0

answered Jul 12 '18 at 9:45

PeterH

5,34612 gold badges33 silver badges64 bronze badges

Thanks Peter! much appreciated, I tried this myself, to only realise that i didn't have the ,1 before the >0. What does that do exactly? – SSP Jul 12 '18 at 10:01
That is part of the INDEX, and specifies which COLUMN to look in, since your array sheet1!F:F you only need to look in the first column, the MATCH part specifies which ROW to look in – PeterH Jul 12 '18 at 10:06

add a comment |

1 Answer 1