How to reference IFNA across 3 or more criterias

[questforexcel]

Hi All,

I need my formula to look across multiple sheets if it does not find value in the current sheet.

So my current formula is

=IFNA(VLOOKUP(I4,Sheet1!$A$2:$D$10,2,0),VLOOKUP(I4,Sheet3!$A$2:$D$10,2,0))

I want this to search across 3 or more data sheets, but i get an error.

=IFNA(VLOOKUP(I4,Sheet1!$A$2:$D$10,2,0),IFNA(VLOOKUP(I4,Sheet3!$A$2:$D$10,2,0),IFNA(VLOOKUP(I4,Sheet1!$A$2:$D$10,2,0)))

Could you please guide and help on how to fix this.

Thank you,

 

[iggydarsa]

try this, I think you are missing the second argument for the last IFNA.

=IFNA(VLOOKUP(I4,Sheet1!$A$2:$D$10,2,0),IFNA(VLOOKUP(I4,Sheet3!$A$2:$D$10,2,0),IFNA(VLOOKUP(I4,Sheet1!$A$2:$D$10,2,0),0)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 another option is

=IFNA(VLOOKUP(I4,VSTACK(Sheet1!$A$2:$D$10,Sheet2!$A$2:$D$10,Sheet3!$A$2:$D$10),2,0),"")

 

[questforexcel]

try this, I think you are missing the second argument for the last IFNA.

=IFNA(VLOOKUP(I4,Sheet1!$A$2:$D$10,2,0),IFNA(VLOOKUP(I4,Sheet3!$A$2:$D$10,2,0),IFNA(VLOOKUP(I4,Sheet1!$A$2:$D$10,2,0),0)))

Thank you, it worked. Although it keeps returning 0 value for the re

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 another option is

=IFNA(VLOOKUP(I4,VSTACK(Sheet1!$A$2:$D$10,Sheet2!$A$2:$D$10,Sheet3!$A$2:$D$10),2,0),"")

Thank you, I just updated and saved that.

 

[Fluff]

What do you want if I4 is not found on the other sheets?

 

[questforexcel]

What do you want if I4 is not found on the other sheets?

Thank you for your reply. 0 is fine. There was an issue with my referencing previously. Thank you both for your kind help.

 

[Fluff]

Glad it's sorted & thanks for the feedback.