Combining V Look Up and IF formula

[gladiator4]

Hi there,

Finding it tricky combining vlookup and if function. I have set out a basic 2 tables below. I am trying to input my result into Column C rows 1 -3. I want to produce "Large", "Medium" or "Small" based on Table 2 below using the vlookup and If function. Can any one help with the formula?

Thanks

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none;}--></style>

	A	B	C
	Table 1
	Unit type	Size	Result
1	Co Work	1000
2	Studio	1500
3	Event	5000
4
5	Table 2
6	Unit Type	Size	Limit
7	Co Work	Small	<500
8	Co Work	Medium	500-1500
9	Co Work	Large	>1500
10	Studio	Small	<500
11	Studio	Medium	500-1500
12	Studio	Large	>1500
13	Event	Small	<2000
14	Event	Medium	2000-4000
15	Event	Large	>4000

<!--StartFragment--> <colgroup><col width="87" span="4" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[oldbrewer]

Hi there,

Finding it tricky combining vlookup and if function. I have set out a basic 2 tables below. I am trying to input my result into Column C rows 1 -3. I want to produce "Large", "Medium" or "Small" based on Table 2 below using the vlookup and If function. Can any one help with the formula?

Thanks

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none;}--></style>

	A	B	C
	Table 1
	Unit type	Size	Result
1	Co Work	1000
2	Studio	1500
3	Event	5000
4
5	Table 2
6	Unit Type	Size	Limit
7	Co Work	Small	<500
8	Co Work	Medium	500-1500
9	Co Work	Large	>1500
10	Studio	Small	<500
11	Studio	Medium	500-1500
12	Studio	Large	>1500
13	Event	Small	<2000
14	Event	Medium	2000-4000
15	Event	Large	>4000

<tbody>
</tbody>

Table 1
	Unit type	Size
	Co Work	1000	499	1499	99999	medium
	Studio	1500	499	1499	99999	large
	Event	5000	1999	3999	99999	large
	Co Work	400	499	1499	99999	small
	Studio	300	499	1499	99999	small
	Co Work	1700	499	1499	99999	large
	Studio	1300	499	1499	99999	medium
	Event	3500	1999	3999	99999	medium
	Co Work	1400	499	1499	99999	medium			small	medium	large
	Studio	2100	499	1499	99999	large		Co Work	499	1499	99999
								Studio	499	1499	99999
								Event	1999	3999	99999
		I used offset match to put the correct parameters
		against every job and an if statement to assign small, medium or large

<colgroup><col span="2"><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[Scott T]

	A	B	C
1
2	Co Work	1,000	medium
3	Studio	1,500	Large
4	Event	5,000	Large

<tbody>
</tbody>

In your second table you should but the points where it changes with out any < or >
Table did not like 0 in cell the small rows should have 0 in column H

	F	G	H
1
2	Co Work	small
3	Co Work	medium	500
4	Co Work	Large	1,500
5	Studio	small
6	Studio	medium	500
7	Studio	large	1,500
8	Event	small
9	Event	medium	2,000
10	Event	large	4,000

<tbody>
</tbody>

In C2 Try this array formula must be entered with Control-Shift-Enter then copy down. Change cell references as need

{=INDEX($G$2:$G$10,MATCH(B2,IF(A2=$F$2:$F$10,$H$2:$H$10),1))}

 

[FormR]

Welcome to the forum!

If you can change the "limits" in table1 as in the example below, then you could give this a try:

Excel 2013
	A	B	C	D	E	F	G
1	Unit Type	Size	Lower Limit		Unit type	Size	Result
2	Co Work	Small	0		Co Work	500	Medium
3	Co Work	Medium	500		Studio	1500	Medium
4	Co Work	Large	1501		Event	5000	Large
5	Studio	Small	0
6	Studio	Medium	500
7	Studio	Large	1501
8	Event	Small	0
9	Event	Medium	2000
10	Event	Large	4001
Sheet1

Cell Formulas
Range	Formula
G2	=LOOKUP(F2,$C$2:$C$10/($A$2:$A$10=E2),$B$2:$B$10)

 

[gladiator4]

Hi all,

Thanks so much for all the help. Considered all the options but ended up going for the simpler Look Up function. Realised this means I have to have my lookup table sorted in ascending order for the formula to work.

Seems to have done the job so thank you for the ideas!

G

If you can change the "limits" in table1 as in the example below, then you could give this a try:

Excel 2013

	A	B	C	D	E	F	G
1	Unit Type	Size	Lower Limit		Unit type	Size	Result
2	Co Work	Small	0		Co Work	500	Medium
3	Co Work	Medium	500		Studio	1500	Medium
4	Co Work	Large	1501		Event	5000	Large
5	Studio	Small	0
6	Studio	Medium	500
7	Studio	Large	1501
8	Event	Small	0
9	Event	Medium	2000
10	Event	Large	4001

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G2 =LOOKUP(F2,$C$2:$C$10/($A$2:$A$10=E2),$B$2:$B$10) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

[/QUOTE]