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.

 

[PhebySamuel]

Hi,

So, I tried working the formula but with no luck. And I just realized that there is something missing in your formula that I need.

Your formula is not taking into account the 3 or 4 criteria it needs to pass which would be an IF statement.

In my previous message where I had summarized what I need to do with my formula in 5 steps, if you look at step #2, #3, and #4 it needs to be an IF statement.

I am able to do the IF statement and all the vlookup indirect everything together, but the issue I am running into is that the IF statement is not taking or does not take a range of cells. It needs a specific cell reference.

Like for example: I want the statement to be like this If(And(Indiatab!B:B<>””,Indiatab!C:C, etc),5,”No Results Found”).

But, it won’t do that. It only accepts a specific cell reference like this If(And(Indiatab!B1<>””,Indiatab!C1, etc),5,”No Results Found”) and I don’t want it that way because let’s say if I move Kangaroo from cell A1 to A10, then the IF statement is still going to search in row 1 and give me the answer from row1 when in fact the animal name is now in row 10 of the India tab.

Does that make sense? Thank you so much for your help so far!

 

[PhebySamuel]

Okay so I will post the formula that I created, I guess, and if you can let me know how to edit it, that would be helpful.

Here’s my formula:

Vlookup(A2,Indirect(“‘“&B$1&”’!A2:I100”),IF(AND(Indiatab!B:B<>””,Indiatab!C:C<>””,etc),5,”No Results Found”),0)

In the summary tab, here’s what the cell references mean:

A2 = Kangaroo
B1 = India

In the India tab, columns B, C, etc are the criteria for each animals, and then column E or F is the Final Result column which is indicated by the number 5 in the above formula.

 

[jasonb75]

Contrary to popular belief, IF tests are not the answer to everything. See if this variation of the formula works correctly.

=IFERROR(LOOKUP("zzz",VLOOKUP($A2,INDIRECT("'"&B$1&"'!A2:F100"),6,0)),"No results found")

 

[jasonb75]

Too late to edit my previous post. I think that the wording of points #3 and #4 in your earlier post may have been the cause of some confusion. Are you saying that it should be "No Result Found" if any of the columns are blank?

Also

and then column E or F is the Final Result

Does this mean that it could be a different column in each sheet? This was something that was not allowed for in the current formula.

 

[PhebySamuel]

Too late to edit my previous post. I think that the wording of points #3 and #4 in your earlier post may have been the cause of some confusion. Are you saying that it should be "No Result Found" if any of the columns are blank?

Also

Does this mean that it could be a different column in each sheet? This was something that was not allowed for in the current formula.

Ok sorry about that last part where I said E or F column! I was just piggy backing off my previous explanations where I first used 3 testing criteria and then I shifted to 4 testing criteria.

To answer your question, No, the final result column is NOT going to be different in every tab. In my actual project, every tab has 4 testing criteria and hence the final result column would be in Column F in EVERY TAB.

But, the order of items (in this case animals) listed in column A COULD be different in EVERY tab. So for example, Australia tab has animals like this: A2 = elephant, A3 = Rabbit, A4 = Kangaroo and India tab has animals like this: A2 = Tiger, A3 = Giraffe, A4 = Elephant, A5 = Kangaroo.

The column A list is where I am running trouble into while creating this formula because I do NOT want to put a specific cell number in my formula for an animal because that cell reference could be a different animal in a different tab. By putting a specific cell reference, my formula won’t pick up the correct animal when I go to a different tab because like I said the order of animals in column A can be different in different tabs and to add to this complexity some tabs can have additional animals not found in other tabs and some tabs may not have the animals found in other tabs!

To answer your other question, Yes, if all the 4 criteria cells for a particular animal is Blank, starting from cells B through E in the individual country tabs, then in the Summary tab I want the answer “No Results Found” instead of it looking in column F i.e. the Final Result column of the individual country tab.

If the 4 criteria cells are not blank, then in the Summary tab I want the answer to come from Column F i.e. the Final Result column.

This is how my formula should work:

Country tab, columns B through E = blank, THEN give me “No Results Found” in the Summary tab.


Country tab, columns B through E = Not blank, THEN give me the answer in Column (the Final Result column) either Pass or Fail, in the Summary tab.

I really hope what I am trying to explain is making sense ?. I never thought an Excel formula could drive me this crazy!

 

[jasonb75]

I never thought an Excel formula could drive me this crazy!

They often can. This is based on an up to date version of office 365 utilising the LET function to avoid repeating the INDIRECT part of the formula multiple times.

=IFERROR(LET(rng,INDIRECT("'"&B$1&"'!A2:F100"),rw,MATCH($A2,INDEX(rng,,1),0),IF(MAX(LEN(INDEX(rng,rw,{2,3,4,5}))),INDEX(rng,rw,6),NA())),"No Result Found")

Hopefully I got it right, I'll be afk for an hour or so, will check when I get back.

 

[PhebySamuel]

They often can. This is based on an up to date version of office 365 utilising the LET function to avoid repeating the INDIRECT part of the formula multiple times.

=IFERROR(LET(rng,INDIRECT("'"&B$1&"'!A2:F100"),rw,MATCH($A2,INDEX(rng,,1),0),IF(MAX(LEN(INDEX(rng,rw,{2,3,4,5}))),INDEX(rng,rw,6),NA())),"No Result Found")

Hopefully I got it right, I'll be afk for an hour or so, will check when I get back.

Okay I guess I am not using that version of excel because I don’t see a LET formula. When I type in Let( … nothing pops up. You know what I mean?

I really apologize for this long question where no answer you provide seems good enough for my scenario lol! I didn’t realize my scenario would require that kind of a complex formula you provided. I was hoping it would work with some If and statements, along with a vlookup but it clearly is not working! All it has done the past 1 week is drive me up the wall!

 

[jasonb75]

It could possibly work with some IF's but there are caveats to certain types of formula. INDIRECT is what is known as a volatile function (there are a few of them).
Any formula that used a volatile function will be recalculated far more often than one that doesn't. Because of this it is preferable to find the most efficient method of calculation, otherwise you will be facing noticeable delays every time you make any kind of change to your data. Unfortunately there is not way of doing what you are asking without the use of INDIRECT so it is now a question of doing the best that we can with it.

This is the same formula as before but without the LET function, if this works as required then it gives me a reference to work from, I'll take another look at alternative methods to see if I can improve on it.

Try entering =FILTER into a cell, if that doesn't come up with a suggested function then it means that you do not have the dynamic array update and that you will need to array confirm this formula with Ctrl Shift Enter.

=IFERROR(IF(MAX(LEN(INDEX(INDIRECT("'"&B$1&"'!A2:F100"),MATCH($A2,INDEX(INDIRECT("'"&B$1&"'!A2:F100"),,1),0),{2,3,4,5}))),INDEX(INDIRECT("'"&B$1&"'!A2:F100"),MATCH($A2,INDEX(INDIRECT("'"&B$1&"'!A2:F100"),,1),0),6),NA()),"No Result Found")

 

[PhebySamuel]

Alright, so I used that formula and I think I maybe seeing some light at the end of this tunnel based on my understanding of the formula provided, BUT, I want to incorporate the IF conditions for columns 2,3,4,5, that you have in the formula.

So the IF conditions will be if(and(column 2<> “”, column 3<> “”, column 4<> “”, column 5<> “”), column6, “No Results Found”.

Is it possible to incorporate these IF conditions into the formula you provided? If it is, then I think I may get the answer I’m looking for.

 

[PhebySamuel]

Alright, so I used that formula and I think I maybe seeing some light at the end of this tunnel based on my understanding of the formula provided, BUT, I want to incorporate the IF conditions for columns 2,3,4,5, that you have in the formula.

So the IF conditions will be if(and(column 2<> “”, column 3<> “”, column 4<> “”, column 5<> “”), column6, “No Results Found”.

Is it possible to incorporate these IF conditions into the formula you provided? If it is, then I think I may get the answer I’m looking for.

Unless, you already had those conditions incorporated into that formula that I probably didn’t catch! My brain has stopped functioning at this point, so I probably didn’t understand that long formula correctly. If you could breakdown that formula that would be helpful!