Alternative formula

[paradise]

Kindly help me in getting alternative formula for 2013 or lower version till 2007.

FILTER(CHOOSE({1,2},'Lookup Data'!$B$4:$B$7,'Lookup Data'!$F$4:$F$7),B3='Lookup Data'!$C$4:$C$7,
FILTER(CHOOSE({1,2},'Lookup Data'!$B$4:$B$7,'Lookup Data'!$K$4:$K$7),B3='Lookup Data'!$H$4:$H$7,""))

 

[p45cal]

The FILTER formula will return multiple rows of results if there are duplicates of what's in B3 in either column C or column H. Is that what you expect of legacy formula, or is a single row result always expected? (It's harder to get multiple row results in Excel 2007 & 2013!)
This, array-entered into a range 1 row by 2 columns will give you a single-row result (the first found):

withdrawn due to cross posting

and should be compatible with both Excel 2007 and Excel 2013.

 

[paradise]

Yeah,it was cross posting.However, recently received the answer.

 

[pecoflyer]

Yeah,it was cross posting.However, recently received the answer.

Forum rules require you add links to ALL cross posts...

 

[paradise]

Ok.
F3
=IFERROR(INDEX('Lookup Data'!$B$4:$B$9,IFERROR(MATCH(B3,'Lookup Data'!$C$4:$C$7,),MATCH(B3,'Lookup Data'!$H$4:$H$9,))),"")

G3
=IF(F3="","",SUMIFS('Lookup Data'!$F$4:$K$9,'Lookup Data'!$C$4:$H$9,B3))
If anyone have different formula getting same results I would appreciate further.

Link

 

[vletm]

paradise
Please, reread and follow Forum Rules - those are for You too.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.