Vlookup with multiple criteria, but with "True" range look up

E

elsorprendente

New Member
Joined
Mar 9, 2011
Messages
4
Hello there,
I know there are hundreds of threads on this topic, but none of them deals with "true" range look ups.
Basic info set (sheet2)
A, B, C, D
Country 1 , 3000 , 4000 , CityX
Country 1 , 4000 , 5000 , CityY
Country 1 , 5000 , 6000 , CityZ
Country 2 , 3000 , 4000 , CityA
Country 2 , 4000 , 5000 , CityB
Country 2 , 5000 , 6000 , CityC

Now, I have a set of other info with variables (sheet1):
Country 1, 3245, <HERE goes which city>

If there was were no multiple countires, my formula would be =VLOOKUP(Sheet1!B:B, Sheet2!B:D,3,TRUE) and I have the city.
I tried to combine with sumproduct, if, index etc, but doesn't work... been trying to solve if for two days, any hints please??
(ps: there are over 30 countries, and altogther 40000 rows on sheet2)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
See if this helps,


Excel Workbook
ABCDEFGH
1Country 130004000CityXCountry 15432CityZ
2Country 140005000CityY
3Country 150006000CityZ
4Country 230004000CityA
5Country 240005000CityB
6Country 250006000CityC
Sheet4
 
Upvote 0
Welcome to the forums!

Another possibility:

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">Country 1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">3000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">4000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">CityX</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">Country 1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">5432</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">CityZ</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">Country 1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">4000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">5000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">CityY</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">Country 1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">5000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">6000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">CityZ</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">Country 2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">3000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">4000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">CityA</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">Country 2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">4000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">5000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">CityB</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">Country 2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">5000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">6000</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">CityC</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet2</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>H1</TH><TD style="TEXT-ALIGN: left">{=INDEX(D1:D6,MATCH(9.99E+307,IF(A1:A6=F1,IF(B1:B6<G1,1,""))))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>
 
Upvote 0
elsorprendente said:
Hello there,
I know there are hundreds of threads on this topic, but none of them deals with "true" range look ups.
Basic info set (sheet2)
A, B, C, D
Country 1 , 3000 , 4000 , CityX
Country 1 , 4000 , 5000 , CityY
Country 1 , 5000 , 6000 , CityZ
Country 2 , 3000 , 4000 , CityA
Country 2 , 4000 , 5000 , CityB
Country 2 , 5000 , 6000 , CityC

Now, I have a set of other info with variables (sheet1):
Country 1, 3245, <HERE city which goes>

If there was were no multiple countires, my formula would be =VLOOKUP(Sheet1!B:B, Sheet2!B:D,3,TRUE) and I have the city.
I tried to combine with sumproduct, if, index etc, but doesn't work... been trying to solve if for two days, any hints please??
(ps: there are over 30 countries, and altogther 40000 rows on sheet2)
Click to expand...

Let A2:D7 on Sheet2 house the table you posted.

Assuming that A2:A7 and B2:C7 are in ascending order...

Sheet2

A2: Country 1

B2: 3245

C2, control+shift+enter, not just enter:

=LOOKUP(B2,IF(Sheet2!$A$2:$A$7=A2,Sheet2!$B$2:$B$7),Sheet2!$D$2:$D$7)
 
Upvote 0
snoopyhr said:
See if this helps,


Excel Workbook
ABCDEFGH
1Country 130004000CityXCountry 15432CityZ
2Country 140005000CityY
3Country 150006000CityZ
4Country 230004000CityA
5Country 240005000CityB
6Country 250006000CityC
Sheet4
Click to expand...


Hi, Thanks for the tip, but it does not seem to work: result is #N/A
(yes, I used ctrl+shft+enter)
 
Upvote 0
MrKowz said:
Welcome to the forums!

Another possibility:

Excel 2003<table style="border: 1px solid rgb(166, 170, 182); border-collapse: collapse; background-color: rgb(255, 255, 255);" rules="all" cellpadding="2"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25"><col><col><col><col><col><col><col><col></colgroup><thead><tr style="color: rgb(22, 17, 32); background-color: rgb(224, 224, 240); text-align: center;"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">Country 1</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">3000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">4000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">CityX</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">Country 1</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">5432</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">CityZ</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">Country 1</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">4000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">5000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">CityY</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">Country 1</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">5000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">6000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">CityZ</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">Country 2</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">3000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">4000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">CityA</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">Country 2</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">4000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">5000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">CityB</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">Country 2</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">5000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">6000</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255);">CityC</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border: 1px solid black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr></tbody></table><table style="border: 1px solid rgb(166, 170, 182); border-collapse: collapse; background-color: rgb(255, 255, 255);" rules="all" cellpadding="2"><thead><tr style="color: rgb(22, 17, 32); background-color: rgb(224, 224, 240); text-align: center;"><th>Sheet2</th></tr></thead><tbody></tbody></table>

<table style="border: 2px solid black; padding: 0.4em; border-collapse: collapse; background-color: rgb(255, 255, 255);" rules="all" cellpadding="2" width="85%"><tbody><tr><td style="padding: 6px;">Array Formulas<table style="border: 1px solid rgb(166, 170, 182); border-collapse: collapse; background-color: rgb(255, 255, 255); text-align: center;" rules="all" cellpadding="2" width="100%"><thead><tr style="color: rgb(22, 17, 32); background-color: rgb(224, 224, 240);"><th width="10">Cell</th><th style="padding-left: 5px; text-align: left;">Formula</th></tr></thead><tbody><tr><th style="color: rgb(22, 17, 32); background-color: rgb(224, 224, 240);" width="10">H1</th><td style="text-align: left;">{=INDEX(D1:D6,MATCH(9.99E+307,IF(A1:A6=F1,IF(B1:B6<g1,1,""></g1,1,"">))))}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</td></tr></tbody></table>
Click to expand...

Thanks for the hint!
I think reference to G1 is missing (e.g. if I put 3028, CityX should be the result), currenlty it is always CityZ, no matter what number is in G1 (and I guess the last if is not needed?)
 
Upvote 0
elsorprendente said:
Thanks for the hint!
I think reference to G1 is missing (e.g. if I put 3028, CityX should be the result), currenlty it is always CityZ, no matter what number is in G1 (and I guess the last if is not needed?)
Click to expand...

Doh - That reference was in there, but the HTML compatability of the boards got it messed up. The formula should be:

=INDEX(D1:D6,MATCH(9.99E+307,IF(A1:A6=F1,IF(B1:B6 < G1,1,""))))

Entered with CSE
 
Upvote 0
Aladin Akyurek said:
Let A2:D7 on Sheet2 house the table you posted.

Assuming that A2:A7 and B2:C7 are in ascending order...

Sheet2

A2: Country 1

B2: 3245

C2, control+shift+enter, not just enter:

=LOOKUP(B2,IF(Sheet2!$A$2:$A$7=A2,Sheet2!$B$2:$B$7),Sheet2!$D$2:$D$7)
Click to expand...

It works!!! great, thank you so much!!!
 
Upvote 0
IN REFERENCE TO THE ABOVE SOLUTION:

Sheet4

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 77px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 72px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana">Country 1 </TD><TD style="TEXT-ALIGN: right">3000</TD><TD style="TEXT-ALIGN: right">4000</TD><TD>CityX</TD><TD></TD><TD style="FONT-FAMILY: Verdana">Country 1</TD><TD style="TEXT-ALIGN: right">5432</TD><TD>CityZ</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana">Country 1 </TD><TD style="TEXT-ALIGN: right">4000</TD><TD style="TEXT-ALIGN: right">5000</TD><TD>CityY</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana">Country 1 </TD><TD style="TEXT-ALIGN: right">5000</TD><TD style="TEXT-ALIGN: right">6000</TD><TD>CityZ</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana">Country 2 </TD><TD style="TEXT-ALIGN: right">3000</TD><TD style="TEXT-ALIGN: right">4000</TD><TD>CityA</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana">Country 2 </TD><TD style="TEXT-ALIGN: right">4000</TD><TD style="TEXT-ALIGN: right">5000</TD><TD>CityB</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana">Country 2 </TD><TD style="TEXT-ALIGN: right">5000</TD><TD style="TEXT-ALIGN: right">6000</TD><TD>CityC</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H1</TD><TD>{=VLOOKUP(F1&" "&G1,CHOOSE({1,2},A1:A6&B1:B6,D1:D6),2,1)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>

I believe the correct formula for this function should be:

{=VLOOKUP(F1&" "&G1,CHOOSE({1,2},A1:A6&" "&B1:B6,D1:D6),2,1)}

as the lookup value uses a space delimiter to concatinate the f1 and g1 values -- so the resulting lookup table needs to use the same lookup key format.

The resulting constructed lookup table array consists of the following values:
<TABLE style="WIDTH: 151pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=200><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2640098 class=xl95 height=20 width=86>Country 1 3000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl94 width=114 align=right>4000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95 height=20>Country 1 4000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl94 align=right>5000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95 height=20>Country 1 5000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl94 align=right>6000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95 height=20>Country 2 3000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl94 align=right>4000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95 height=20>Country 2 4000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl94 align=right>5000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl95 height=20>Country 2 5000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl94 align=right>6000</TD></TR></TBODY></TABLE>

(*Note that column "C" (upper bounds) is not used in the lookup logic at all. This would be a problem if the lookup ranges were not contiguous.)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA copy data from multiple workbooks based on criteria
Replies
6
Views
2K
Jendjieh
J
M
  • Question Question
separate multiple separated ranges into multiple sheets
Replies
0
Views
445
Maklil
M
M
  • Question Question
Having Trouble Tackling a larger formula: need to return multiple values based on multiple criteria
Replies
3
Views
870
RasGhul
RasGhul
B
  • Question Question
vba help needed, multiple criteria based on cell values to filter single field in range
Replies
2
Views
855
bentez2003
B
S
  • Question Question
Look up with multiple criteria - Index Match?
Replies
6
Views
282
soilwork6
S

Forum statistics

Threads
1,224,616
Messages
6,179,909
Members
452,949
Latest member
beartooth91

Share this page

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
Back
Top