if vlookup

[rhombus4]

If I enter a DIY product in cell A1 (e.g. Hammer), I have a vlookup formula in Cell C1 which tells me what category that item is (e.g. Tool). In cell C2 I have a vlookup formula which will then tell me who is responsible for that item. (eg. James)

=IF(C1="Tools",VLOOKUP(A1,Sheet2!A1:B100,2,0)).

However each person has a set allocation of jobs done, what I need to incorporate in the above vlookup is to look at the formula above and if James has reached his allocation then the task will be allocated to the next person which is John

The allocations are in cells A31:F32

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=384 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>James</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>John</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Dave</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Harry</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Tom</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Allocation</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>50</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>60</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>55</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>45</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>30</TD></TR></TBODY></TABLE>

 

[rhombus4]

Thanks! That one worked

Except one thing, reason I only had Tools etc was because the other option materials didnt matter. If someone enters an incorrect value in A1 is it possible to have it say incorrect item. as it is now when A1 is deleted I get NA and it crashes

Before I had a different formula

=IF(ISNA(VLOOKUP(A1,'Sheet2'!$A$1:$C$577,2,0)),"Incorrect Item",VLOOKUP(A1,'Sheet2'!$A$1:$C$577,2,0)) is it possible to incorporate this into one of the other formulas

 

[Comfy]

The easiest way would be to do the following:

=IF(ISNA(IF(HLOOKUP(IF(C1="Tools",VLOOKUP(A1,Sheet2!A1:B100,2,0)),$B$31:$F$33,3,FALSE)<1,INDEX($B$31:$F$33,1,MATCH(TRUE,$B$33:$F$33>0,0)),IF(C1="Tools",VLOOKUP(A1,Sheet2!A1:B100,2,0)))),"Invalid Item",IF(HLOOKUP(IF(C1="Tools",VLOOKUP(A1,Sheet2!A1:B100,2,0)),$B$31:$F$33,3,FALSE)<1,INDEX($B$31:$F$33,1,MATCH(TRUE,$B$33:$F$33>0,0)),IF(C1="Tools",VLOOKUP(A1,Sheet2!A1:B100,2,0))))

But it might be beneficial to analyse your other lookups, i.e C1.

What is the Vlookup that you have in C1?

 

[rhombus4]

The easiest way would be to do the following:

=IF(ISNA(IF(HLOOKUP(IF(C1="Tools",VLOOKUP(A1,Sheet2!A1:B100,2,0)),$B$31:$F$33,3,FALSE)<1,INDEX($B$31:$F$33,1,MATCH(TRUE,$B$33:$F$33>0,0)),IF(C1="Tools",VLOOKUP(A1,Sheet2!A1:B100,2,0)))),"Invalid Item",IF(HLOOKUP(IF(C1="Tools",VLOOKUP(A1,Sheet2!A1:B100,2,0)),$B$31:$F$33,3,FALSE)<1,INDEX($B$31:$F$33,1,MATCH(TRUE,$B$33:$F$33>0,0)),IF(C1="Tools",VLOOKUP(A1,Sheet2!A1:B100,2,0))))

But it might be beneficial to analyse your other lookups, i.e C1.

What is the Vlookup that you have in C1?

The VLOOKUP in C1 is VLOOKUP(A1,Sheet2!A1:B100,3,0))



Thanks, it works great except one thing,

If C1=Tools - works as it should
If C1= typo error or doesnt appear in the Vlookup range - Invalid item as it should,

However if C1 = Materials I just need C2 to be blank.

 

[Comfy]

Alittle rejigging gave me this:

=IF(C1="Tools",IF(ISNA(IF(HLOOKUP(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),$B$31:$F$33,3,FALSE)<1,INDEX($B$31:$F$33,1,MATCH(TRUE,$B$33:$F$33>0,0)),HLOOKUP(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),$B$31:$F$33,1,FALSE))),"Invalid Item",IF(HLOOKUP(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),$B$31:$F$33,3,FALSE)<1,INDEX($B$31:$F$33,1,MATCH(TRUE,$B$33:$F$33>0,0)),HLOOKUP(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),$B$31:$F$33,1,FALSE))),"")

Let me know the outcome

 

[rhombus4]

Alittle rejigging gave me this:

=IF(C1="Tools",IF(ISNA(IF(HLOOKUP(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),$B$31:$F$33,3,FALSE)<1,INDEX($B$31:$F$33,1,MATCH(TRUE,$B$33:$F$33>0,0)),HLOOKUP(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),$B$31:$F$33,1,FALSE))),"Invalid Item",IF(HLOOKUP(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),$B$31:$F$33,3,FALSE)<1,INDEX($B$31:$F$33,1,MATCH(TRUE,$B$33:$F$33>0,0)),HLOOKUP(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),$B$31:$F$33,1,FALSE))),"")

Let me know the outcome

Sorry made an error in the post above

Instead of The VLOOKUP in C1 is VLOOKUP(A1,Sheet2!A1:B100,3,0))

c1 is =VLOOKUP(A1,Sheet2!A1:C100,3,0).

When I enter an item in A1 that is a MAterial then C1 says Material and C2 is blank as is required.

However when I enter an incorrect item or it is left blank C1 and C2 are #N/A

Maybe need to amend C1 to include a ISNA formula as well

 

[Comfy]

Have you changed the formula for your typo?

This is what I get:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Lemon</td><td style="text-align: right;;"></td><td style=";">Tools</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Invalid Item</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">James</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">John</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Dave</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Harry</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Tom</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Allocation</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">50</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">60</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">55</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">45</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">Left</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">0</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">32</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">7</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[rhombus4]

Have you changed the formula for your typo?

This is what I get:

Excel 2007<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></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Lemon</TD><TD style="TEXT-ALIGN: right"></TD><TD>Tools</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Invalid Item</TD></TR></TBODY></TABLE>

Sheet1​

Excel 2007<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></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></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">31</TD><TD style="BORDER-RIGHT: black 1px solid; 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: center">James</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: center">John</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: center">Dave</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: center">Harry</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: center">Tom</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">32</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Allocation</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: center">50</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: center">60</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: center">55</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: center">45</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: center">30</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">33</TD><TD style="BORDER-TOP: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">Left</TD><TD style="BORDER-TOP: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">1</TD><TD style="BORDER-TOP: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">0</TD><TD style="BORDER-TOP: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">32</TD><TD style="BORDER-TOP: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">8</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">7</TD></TR></TBODY></TABLE>

Sheet1​

Apologies for any confusion

The formula in C1 has always been the same, I mistyped it here.

If I entered Lemon in A1 and it was a material (in the vlookup table) then C1 would say material and C2 would be blank.

If I enter hammer in A1 C1 would say tools and C2 would give a name depending on the formula.

If A1 is blank or incorrect entry C1 needs to be blank and C2 invalid item

If I entered Red in A1 and it wasnt a Tool or Material it would

 

[Comfy]

I think I understand all the variables.

Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">lemon</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Invalid Item</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">ISNA(<font color="Red">VLOOKUP(<font color="Green">A1,Sheet2!$A$1:$C$100,3,FALSE</font>)</font>),"",VLOOKUP(<font color="Red">A1,Sheet2!$A$1:$C$100,3,FALSE</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">{=IF(<font color="Blue">C1="Tools",IF(<font color="Red">ISNA(<font color="Green">IF(<font color="Purple">HLOOKUP(<font color="Teal">VLOOKUP(<font color="#FF00FF">A1,Sheet2!$A$1:$B$100,2,0</font>),$B$31:$F$33,3,FALSE</font>)<1,INDEX(<font color="Teal">$B$31:$F$33,1,MATCH(<font color="#FF00FF">TRUE,$B$33:$F$33>0,0</font>)</font>),HLOOKUP(<font color="Teal">VLOOKUP(<font color="#FF00FF">A1,Sheet2!$A$1:$B$100,2,0</font>),$B$31:$F$33,1,FALSE</font>)</font>)</font>),"Invalid Item",IF(<font color="Green">HLOOKUP(<font color="Purple">VLOOKUP(<font color="Teal">A1,Sheet2!$A$1:$B$100,2,0</font>),$B$31:$F$33,3,FALSE</font>)<1,INDEX(<font color="Purple">$B$31:$F$33,1,MATCH(<font color="Teal">TRUE,$B$33:$F$33>0,0</font>)</font>),HLOOKUP(<font color="Purple">VLOOKUP(<font color="Teal">A1,Sheet2!$A$1:$B$100,2,0</font>),$B$31:$F$33,1,FALSE</font>)</font>)</font>),IF(<font color="Red">C1="","Invalid Item",""</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[rhombus4]

Thanks so much much appreciated

Note to self double check before posting to avoid confusion