Vlookup, Index, Match

[PhebySamuel]

Hi All,

I need some help creating a formula. So I’ll explain the data in my document and then I’ll explain what I want out of my formula!

My document has several tabs. The first tab is the summary tab where the formulas will go. All the other tabs will feed into the formulas in the first tab. So, all the other tabs are certain tests that I am performing and getting a pass or fail result based on some criteria that I have created.

For example: tab #2 has lets say column A that has a list of things/places/animals etc. Columns B through E in tab #2 are 4 different criteria for the items in column A. If they pass the 4 criteria, each of the cells i.e. B1 C1 D1 and E1 will have Pass, and similarly if they fail one of the criteria then that cell will have Fail listed in it. Now column F is the summary result column for just this tab. Which means if in row1, if there is even one Fail among the 4 criteria, then F1 will show Fail as a summary for that particular item. F1 can only show pass if all the 4 criteria are a pass.

If I do not have information to test the 4 criteria, then those 4 boxes will be blank

I have multiple such testing tabs starting from tab#2! Each tab lets say represents a country.

Now, going back to the very first tab which is the summary tab for all tabs is very all the formulas are going to be. In this tab, I have listed out all the items such as things/animals in column A and I have listed out the countries (that start from tab #2) in rows B1 through let’s say G1. Basically created a table outline, if that makes sense.

I want to create a formula, that will lookup the items listed in column A of tab 1 in the specific country’s tab starting from tab #2 in its Column A and once it finds that item in tab #2, then it will look across the row to look for the 4 criteria results. If the 4 criteria boxes are filled out, then it will give me the result listed in column F for that specific item. If the 4 criteria boxes are blank, then it will return “No Result Found” message.

 

[jasonb75]

Unless, you already had those conditions incorporated into that formula that I probably didn’t catch!

That is what the MAX(LEN(.....)) part does, it is set to look for at least one non blank cell in columns B:D of the matching row.

I'll do a breakdown of the formula for you when I get home later.

 

[PhebySamuel]

That is what the MAX(LEN(.....)) part does, it is set to look for at least one non blank cell in columns B:D of the matching row.

I'll do a breakdown of the formula for you when I get home later.

Thank you so much! Hopefully once I understand the Max(Len part maybe I can update it to give me the result I need.

Thanks a ton for your help!!

 

[jasonb75]

Looking back at where you used IF(AND(... to show what you thought was needed, I think that I might have misunderstood the blanks requirement.

The MAX method was looking to see if there was at least one non blank in the pass / fail columns. Looking at what you tried it appears that it should be no blanks at all, in which case it would need to be MIN(...)=0 instead if MAX(...) as below. I've also shortened it a bit, the previous formula was the longhand equivalent to the LET formula, there were parts that were only needed to make it work with LET so I've taken them out.

=IFERROR(IF(MIN(LEN(INDEX(INDIRECT("'"&B$1&"'!B2:E100"),MATCH($A2,INDIRECT("'"&B$1&"'!A2:A100"),0),0)))=0,INDIRECT("'"&B$1&"'!F"&MATCH($A2,INDIRECT("'"&B$1&"'!A2:A100"),0)),NA()),"No Result Found")