LOOKUP function (2) questions

1
=index(indirect("'"&d5&"'!$a$8:$u$1999"),match($a7,indirect("'"&d5&"'!$a$8:$a$1999"),0),match("total",indirect("'"&d5&"'!$a$8:$u$8"),0))
2
=iferror(index('1'!$a$8:$u$1999,match($a8,'1'!$a$8:$a$1999,0),match("total",'1'!$a$8:$u$8,0)),"")
im not sure why you used
,match("total",'1'!$a$8:$u$8,)the correct syntax is
,match("total",'1'!$a$8:$u$8,0) finds exact match

Originally Posted by martindwilson

im not sure why you used
,match("total",'1'!$a$8:$u$8,)as that needs sorted data whereas
,match("total",'1'!$a$8:$u$8,0) finds exact match

Actually,
match("total",'1'!$a$8:$u$8,)
Is actually doing Exact Match
The 3rd argument is not actually omitted, notice the comma
Not sure exactly, but I think that makes the 3rd argument a Null, which is then considered 0.

This is omitted
match("total",'1'!$a$8:$u$8)
Which would then become the "closest match"

yep i know hence my edit,but its usually best to do what the help file says