Lookup IF this, AND that AND that AND...

Working Man

New Member
Joined
Dec 23, 2015
Messages
3
Hello Wizards! In advance thank you so much for any help. I am self taught and have gotten along "ok" (I spend hours to complete what you all would deem simple) but my current need is too complex for me to think thru. Here goes:
I am attempting to create a lookup from a cover sheet to another sheet in the same workbook. In the lookup sheet there is a list of names (col A) with a table to its right. Columns "J" thru "N" has values related to the name at left of each row. I need to identify which names return a value greater than "0" from column "J", which names return a value greater than "10" from column "K", which names return a value greater than "25" from column "L", which names return a value greater than "100" from column "M" & which names return a value greater than "1000" from column "N".

So to properly express the sheet to be "looked" at: column A has a list of names and each one coincides with a tab in the workbook. The lookup worksheet columns J,K,L,M, & N each lookup data from those tabs and place the data in the J,K,L,M,N cell to the right of each given name. I am trying to have the workbook return the names that meet most of the criteria listed above. So I am not looking to return just one name... unless that happens to be the answer to the criteria list. I am hoping several names will be returned in list form from this lookup.

Unfortunately, I am not as fluent in excel as I wish to be. I hope I explained this well enough. It seems to me as if it is a "lookup IF, AND, AND, AND" kind of a statement. Thanks to you all for entertaining this challenge.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.
 

Working Man

New Member
Joined
Dec 23, 2015
Messages
3
Thank You for taking interest Mr. Maabadi. I just muttled through the xl2bb process so please let me know if I got it right.
I am adding to the criteria for the purposes of this sample sheet. In my initial description the request stated ">0 @ column J, >10 @ K, >25 @ L, >100 @ M, & >1000 @ N". Please add ">10,000 @ col O" into the formula. Thanks
I am hoping to generate a table that looks like
SUN _ _ _ _ _ 46,400
Mercury 1.22 _ _ _ _ 220,000
Earth _ _ _ 174.94 1500 _
Mars _ 15.16 66.09 462.82 _ 188,000
Saturn _ 11.99 29.07 _ _ 16,500
Uranus _ _ _ _ _
Neptune _ 15.96 25.33 _ _ 12,700

Please note that Jupiter and Pluto didn't satisfy any criteria so they were excluded from the return.

 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Are you want this:
Book1
ABCDEFGHIJKLMNOP
1
2
3SUNNASA2/23/20219:25:00 AM48810.87$27,637.62177%0.00943460.28-0.24-5.33-0.852.36404.7846,400.00
4
5MERCURYNASA2/23/20219:25:00 AM1558.29$1,098.81142%301.22-1.51-12.7126.01464.18220,000.00
6
7EARTHNASA2/23/20219:25:00 AM0.95$0.37256%99.874599.87-1.56-6.427.62174.941,500.004,300.00
8
9MARSNASA2/23/20219:25:00 AM220.029.182398%1.906414.06-12.5515.1666.09462.82878.34188,000.00
10
11JUPITERNASA2/23/20219:25:00 AM176.11193.8391%0.958299198.66-0.64-11.04-17.9125.58131.395,800.00
12
13SATURNNASA2/23/20219:25:00 AM500.61733.6568%-0.14235-87.21-2.4911.9929.0715.9632.25-10.27
14
15URANUSNASA2/23/20219:25:00 AM26.2833.5878%8.21248.02-3.12-5.17-18.323.71562.5516,500.00
16
17NEPTUNENASA2/23/20219:25:00 AM500.610.39127309%146.15481371.62-3.5615.9625.3341.67447.5812,700.00
18
19PLUTONASA2/23/202111:23:00 AM183.8465.44281%0.2497150.19-1.21-13.77-22.983.03-37.5970.57
20
21
22MERCURY1.22----$ 220,000.00
23MARS-15.1666.09462.82-$ 188,000.00
24URANUS-----$ 16,500.00
25PLUTO----- -
26SUN-----$ 46,400.00
27EARTH---174.941500 -
28JUPITER----- -
29SATURN-11.9929.07-- -
30NEPTUNE-15.9625.33--$ 12,700.00
31
Sheet2
Cell Formulas
RangeFormula
B22:B30B22=IF(VLOOKUP($A22,$A$3:$O$19,10,FALSE)>0,VLOOKUP($A22,$A$3:$O$19,10,FALSE),"-")
C22:C30C22=IF(VLOOKUP($A22,$A$3:$O$19,11,FALSE)>10,VLOOKUP($A22,$A$3:$O$19,11,FALSE),"-")
D22:D30D22=IF(VLOOKUP($A22,$A$3:$O$19,12,FALSE)>25,VLOOKUP($A22,$A$3:$O$19,12,FALSE),"-")
E22:E30E22=IF(VLOOKUP($A22,$A$3:$O$19,13,FALSE)>100,VLOOKUP($A22,$A$3:$O$19,13,FALSE),"-")
F22:F30F22=IF(VLOOKUP($A22,$A$3:$O$19,14,FALSE)>1000,VLOOKUP($A22,$A$3:$O$19,14,FALSE),"-")
G22:G30G22=IF(VLOOKUP($A22,$A$3:$O$19,15,FALSE)>10000,VLOOKUP($A22,$A$3:$O$19,15,FALSE),"-")
 

Working Man

New Member
Joined
Dec 23, 2015
Messages
3
Hey Mr Maabadi,
Sorry took so long to reply. I found myself needed in the field. I appreciate that. It is very helpful. However, I was hoping the return would simply be a list of names from the "A" column that complied with the lookup specs. In this example "PLUTO" has zero fields meeting the criteria. So the cover sheet would receive the list of names excluding "PLUTO" and the values matching the criteria. Just simply a list of the planets (lets say) that meet the criteria. I don't even know if this is possible in Excel?
I really can't thank you enough for your efforts. This is huge for me!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Are you want this:
Book1
ABCDEFGHIJKLMNO
1
2
3SUNNASA2/23/20210.39236148810.8727637.621.770.00943460.28-0.24-5.33-0.852.36404.7846400
4
5MERCURYNASA2/23/20210.3923611558.291098.811.42301.22-1.51-12.7126.01464.18220000
6
7EARTHNASA2/23/20210.3923610.950.372.5699.874599.87-1.56-6.427.62174.9415004300
8
9MARSNASA2/23/20210.392361220.029.1823.981.906414.06-12.5515.1666.09462.82878.34188000
10
11JUPITERNASA2/23/20210.392361176.11193.830.910.958299198.66-0.64-11.04-17.9125.58131.395800
12
13SATURNNASA2/23/20210.392361500.61733.650.68-0.14235-87.21-2.4911.9929.0715.9632.25-10.27
14
15URANUSNASA2/23/20210.39236126.2833.580.788.21248.02-3.12-5.17-18.323.71562.5516500
16
17NEPTUNENASA2/23/20210.392361500.610.391273.09146.154871.62-3.5615.9625.3341.67447.5812700
18
19PLUTONASA2/23/20210.474306183.8465.442.810.2497150.19-1.21-13.77-22.983.03-37.5970.57
20
21
22SUN-----$ 46,400.00
23MERCURY1.22----$220,000.00
24EARTH---174.941500 -
25MARS-15.1666.09462.82-$188,000.00
26      
27SATURN-11.9929.07-- -
28URANUS-----$ 16,500.00
29NEPTUNE-15.9625.33--$ 12,700.00
30      
31
Sheet2
Cell Formulas
RangeFormula
A22:A30A22=IF(OR(INDIRECT(ADDRESS(ROWS($A$3:A3)*2+1,10,4))>0,INDIRECT(ADDRESS(ROWS($A$3:A3)*2+1,11,4))>10,INDIRECT(ADDRESS(ROWS($A$3:A3)*2+1,12,4))>25,INDIRECT(ADDRESS(ROWS($A$3:A3)*2+1,13,4))>100,INDIRECT(ADDRESS(ROWS($A$3:A3)*2+1,14,4))>1000,INDIRECT(ADDRESS(ROWS($A$3:A3)*2+1,15,4))>10000),INDIRECT(ADDRESS(ROWS($A$3:A3)*2+1,1,4)),"")
B22:B30B22=IFNA(IF(VLOOKUP($A22,$A$3:$O$19,10,FALSE)>0,VLOOKUP($A22,$A$3:$O$19,10,FALSE),"-"),"")
C22:C30C22=IFNA(IF(VLOOKUP($A22,$A$3:$O$19,11,FALSE)>10,VLOOKUP($A22,$A$3:$O$19,11,FALSE),"-"),"")
D22:D30D22=IFNA(IF(VLOOKUP($A22,$A$3:$O$19,12,FALSE)>25,VLOOKUP($A22,$A$3:$O$19,12,FALSE),"-"),"")
E22:E30E22=IFNA(IF(VLOOKUP($A22,$A$3:$O$19,13,FALSE)>100,VLOOKUP($A22,$A$3:$O$19,13,FALSE),"-"),"")
F22:F30F22=IFNA(IF(VLOOKUP($A22,$A$3:$O$19,14,FALSE)>1000,VLOOKUP($A22,$A$3:$O$19,14,FALSE),"-"),"")
G22:G30G22=IFNA(IF(VLOOKUP($A22,$A$3:$O$19,15,FALSE)>10000,VLOOKUP($A22,$A$3:$O$19,15,FALSE),"-"),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,128,126
Messages
5,628,858
Members
416,345
Latest member
sayad

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