Explain a Lookup formula

[gheyman]

In this formula, can someone explain this part "{0,0:1"


=IFNA((+SUMIFS(INDEX(Qty_CostSource!$B$15:$AK$83564,,MATCH(IF($I28="","",(LOOKUP(I28,{0,0;1,"1q4";5,"5q9";10,"10q24";25,"25q49";50,"50q74";75,"75q99";100,"100q199";200,"200q249";250,"250q499";500,"500q749";750,"750q999";1000,"1000q1999";2000,"2000q2999";3000,"3000q4999";5000,"5000q5999";6000,"6000q7499";7500,"7500q9999";10000,"10000q12999";13000,"13000q14999";15000,"15000q19999";20000,"20000q22999";23000,"23000q25999";26000,"26000q29999"}))),Qty_CostSource!$B$15:$AK$15,0)),INDEX(Qty_CostSource!$B$15:$AK$83564,,MATCH(IF($I30="","",(LOOKUP(I30,{0,0;1,"1q4";5,"5q9";10,"10q24";25,"25q49";50,"50q74";75,"75q99";100,"100q199";200,"200q249";250,"250q499";500,"500q749";750,"750q999";1000,"1000q1999";2000,"2000q2999";3000,"3000q4999";5000,"5000q5999";6000,"6000q7499";7500,"7500q9999";10000,"10000q12999";13000,"13000q14999";15000,"15000q19999";20000,"20000q22999";23000,"23000q25999";26000,"26000q29999"}))),Qty_CostSource!$B$15:$AK$15,0)),">0",INDEX(Qty_CostSource!$B$15:$AK$83564,,1),$C22)),"")

I am trying to copy the formula and use this in another place, but I cannot seem to get it work in the new workbook

Thanks

 

[Max1616]

Hello,

The lookup formula will lookup whatever the number is in I28, and return the corresponding value that lives between the brackets:

if I28 = 0 then it will return 0 ----> 0,0 means if zero then return 0
if I28 = 1-4 then it will return 1q4 ----> ;1,"1q4" means if I28 = 1 through the next stated value, then return "1q4"
if I28 = 5-9 then it will return 5q9 ----> ;5,"5q9" means if I28 = 5 through the next stated value then return "5q9"
etc...


You can type anything you'd like between the quotes, and the lookup value will return whatever is between those quotes.

Everything between the brackets {} is the range you need to match your value to, and the lookup value the formula needs to return .

Here's a simpler example to look at:

=LOOKUP(A2,{0,0;1,"Bucket1";5,"Bucket2";10,"Bucket3";25,"Bucket4"})

If A2 is 0 it will return 0
If A2 is 1-5 it will return "Bucket1"
If A2 is 6-10 it will return "Bucket2"
If A2 is 11-24 it will return "Bucket3"
If A2 is 25+ it will return "Bucket4"

 

[jasonb75]

this part "{0,0:1"

If you have the same typo in your copied formula then that might go some way to explain why it is not working. In the part of your post that I have quoted, you have used a colon where the formula uses a semicolon.

That part of the formula is creating a table within the formula rather than having a reference table in a sheet. It can work but it is a messy way of doing it. Effectively a comma separates each column of data in the array while a semicolon separates each row.

Personally, I think you would be better off using the method that I have shown below, less messy and easier to manage. Note that the formula is entered into the bottom of the table but shows blank due to an absence of source data for it to return a valid result.

It is possible that further improvements could be made to your formula, however I have only looked at the part that relates to the original question.

Book1
	A	B
2	0	0
3	1	1q4
4	5	5q9
5	10	10q24
6	25	25q49
7	50	50q74
8	75	75q99
9	100	100q199
10	200	200q249
11	250	250q499
12	500	500q749
13	750	750q999
14	1000	1000q1999
15	2000	2000q2999
16	3000	3000q4999
17	5000	5000q5999
18	6000	6000q7499
19	7500	7500q9999
20	10000	10000q12999
21	13000	13000q14999
22	15000	15000q19999
23	20000	20000q22999
24	23000	23000q25999
25	26000	26000q29999
26
27
Sheet1

Cell Formulas
Range	Formula
B27	B27	=IFNA((+SUMIFS(INDEX(Qty_CostSource!$B$15:$AK$83564,,MATCH(IF($I28="","",LOOKUP(I28,$A$4:$B$25)),Qty_CostSource!$B$15:$AK$15,0)),INDEX(Qty_CostSource!$B$15:$AK$83564,,MATCH(IF($I30="","",LOOKUP(I30,$A$2:$B$25)),Qty_CostSource!$B$15:$AK$15,0)),">0",INDEX(Qty_CostSource!$B$15:$AK$83564,,1),$C22)),"")