Vlookup, Index, Match

PhebySamuel

New Member
Joined
Sep 21, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

PhebySamuel

New Member
Joined
Sep 21, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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")
 

Forum statistics

Threads
1,148,364
Messages
5,746,276
Members
424,003
Latest member
paaskanama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top