merging 2 formulas

R

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
586
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Currently have a formula

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

I used to have a formula

=IF(ISNA(VLOOKUP(A1,'Sheet2'!$A$1:$B$100,2,0)),"Incorrect Item",VLOOKUP(A1,'Sheet2'!$A$1:$B$100,2,0))

There are only 3 Categories, Tools, Materials and if it isnt in the list will say Incorrect Item what I need is to incorporate the ISNA or similar formula into the 1st formula:)
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Maybe Ive not explained it properly. This was the initial thread which I thought was correct but then realised I need not only the if to say if C1=Tools but also

If C1=Materials it will say (IF(C1="Materials",VLOOKUP(A1,Sheet2!A1:C100,3,0) and if it doesnt appear in A1:C100 it will say "invalid Item"
 
Upvote 0
Rhombus4,

If I understand correctly what you want, then I think this can help you:

<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;">1</td><td style=";">XYZ</td><td style="text-align: right;;"></td><td style=";">Invalid</td><td style="text-align: right;;"></td><td style=";">Hammer</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=";">Incorrect Item</td><td style="text-align: right;;"></td><td style=";">Test</td><td style=";">Materials</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="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Gold</td><td style=";">Tools</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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;">31</td><td style="text-align: right;;"></td><td style="background-color: #D99795;;">James</td><td style="background-color: #D99795;;">John</td><td style="background-color: #D99795;;">Dave</td><td style="background-color: #D99795;;">Harry</td><td style="background-color: #D99795;;">Tom</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">Allocation</td><td style="text-align: right;background-color: #D99795;;">50</td><td style="text-align: right;background-color: #D99795;;">60</td><td style="text-align: right;background-color: #D99795;;">55</td><td style="text-align: right;background-color: #D99795;;">45</td><td style="text-align: right;background-color: #D99795;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">Left</td><td style="text-align: right;background-color: #D99795;;">0</td><td style="text-align: right;background-color: #92D050;;">0</td><td style="text-align: right;background-color: #92D050;;">0</td><td style="text-align: right;background-color: #92D050;;">8</td><td style="text-align: right;background-color: #92D050;;">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 /><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,E1:F3,2,0</font>)</font>),"Invalid",VLOOKUP(<font color="Red">A1,E1:F3,2,0</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">OR(<font color="Red">C1={"Materials";"Tools"}</font>),
IF(<font color="Red">
HLOOKUP(<font color="Green">
IF(<font color="Purple">OR(<font color="Teal">C1={"Materials";"Tools"}</font>),
VLOOKUP(<font color="Teal">A1,Sheet2!A1:B100,2,0</font>)
</font>),
$B$31:$F$33,3,0</font>)<1,
INDEX(<font color="Green">$C$31:$F$33,1,MATCH(<font color="Purple">TRUE,$C$33:$F$33>0,0</font>)</font>),
IF(<font color="Green">OR(<font color="Purple">C1={"Materials";"Tools"}</font>),VLOOKUP(<font color="Purple">A1,Sheet2!A1:B100,2,0</font>)</font>)</font>),
"Incorrect Item"</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 />
If not, give me more information (with examples).

Use the link below for helper to post your examples.

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

Markmzz
 
Upvote 0
used your data in cells E1:F3

If i enter hammer in A1 it works ok
Gold shows tools but then #n/a in C2
If i enter test C1 is MAterials but #n/a still in C2
 
Upvote 0
rhombus4 said:
used your data in cells E1:F3

If i enter hammer in A1 it works ok
Gold shows tools but then #n/a in C2
If i enter test C1 is MAterials but #n/a still in C2
Click to expand...


Rhombus4,

In my sheet, the error only occurs when I delete all the data of an item (i.e. Gold) of worksheet sheet2.

Then, I have the error #n/a in C2 (like you).

Markmzz
 
Upvote 0
rhombus4 said:
used your data in cells E1:F3

If i enter hammer in A1 it works ok
Gold shows tools but then #n/a in C2
If i enter test C1 is MAterials but #n/a still in C2
Click to expand...
Can you put together a sample file so we can see how your sheet is setup?

You can use a free file host if need be.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Search vlookup more than 1 column
2 3
Replies
23
Views
557
DanteAmor
DanteAmor
S
  • Solved
Pairwise Comparison Table
Replies
4
Views
386
Samgraphics
S
S
  • Solved
Copying Formulas with Formula.R1C1 Results in Rows and Columns Incrementing by 2
Replies
5
Views
608
Alex Blakenburg
A
R
  • Solved
Count Unique Values excluding blanks and one value
Replies
6
Views
116
Domenic
D
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
312
etaf
etaf

Forum statistics

Threads
1,224,613
Messages
6,179,901
Members
452,948
Latest member
Dupuhini

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