0

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))
  • this will bring back a value of 29

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

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)?

  • You are not using INDEX correctly – PeterH Jul 12 '18 at 9:33
  • 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
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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.