Using nested IF and Match to return multiple values if true/false

[esnelson426]

I want to have a formulat that put one of two different values ("IN" or "T") in the column if row A on US FULL SERVICE-Mdwst-West sheet matches the values in two different columns on the “Hotel accounts” sheet.

I think it’s more like a nested IF function….with a match function included in it… I’ve got the match part figured out but it only returns one value. I want it to say if it matches put a IN in the column, OR if it matches another range put a T in the column….

this is what i tried but it will only return one true and false value....
=IF(ISERROR(MATCH(D3,B$3:B$8,<wbr style="font-family: arial, sans-serif;">0)),"No","Yes (" & MATCH(D3,B$3:B$8,0) & ")")

 

[Andrew Poulsom]

Welcome to MrExcel.

What do you want to match, and in which ranges (you only have one lookup range in your formula)?

 

[esnelson426]

for example I want to see if cell A45 on sheet "US FULL SERVICE-Mdwst-West" matches a value on sheet "Hotel Accounts" in A2:A25 and if it does put "IN" in cell B45 AND if A45 matches a value on sheet "Hotel Accounts" in B2:B25 then put a T in cell B45

 

[esnelson426]

for example I want to see if cell A45 on sheet "US FULL SERVICE-Mdwst-West" matches a value on sheet "Hotel Accounts" in A2:A25 and if it does put "IN" in cell B45 AND if A45 matches a value on sheet "Hotel Accounts" in B2:B25 then put a T in cell B45

Would a VLOOKUP be better for this??

 

[Andrew Poulsom]

Does this work for you?

=IF(COUNTIF('US FULL SERVICE-Mdwst-West'!A2:A25,A45),"IN",IF(COUNTIF('US FULL SERVICE-Mdwst-West'!B2:B25,A45),"T",""))

 

[esnelson426]

Does this work for you?

=IF(COUNTIF('US FULL SERVICE-Mdwst-West'!A2:A25,A45),"IN",IF(COUNTIF('US FULL SERVICE-Mdwst-West'!B2:B25,A45),"T",""))

That didn't work - the cell was blank....Maybe I am doing it backwards? I tried this instead - =IF(MATCH('HOTEL ACCOUNTS'!A2:A25,A45),"IN",IF(MATCH('HOTEL ACCOUNTS'!A2:A25,A45),"T")) and got N?A
I am looking at a single value on one sheet and checking for a match in a range on another....

 

[Andrew Poulsom]

When my formula returns null, what's in A45 and what's in A2:B25 on sheet 'US FULL SERVICE-Mdwst-West'?

 

[esnelson426]

When my formula returns null, what's in A45 and what's in A2:B25 on sheet 'US FULL SERVICE-Mdwst-West'?

This might help .... pick a value in row A - look in Sheet 2 at row A and if there is a match mark "IN" in row B on Sheet 1....then look in sheet 2 at row B and if the value in sheet one row A matches one of those then mark "T" on sheet 1

this is sheet 1 or US FULL SERVICE-Mdwst-WEST

[TABLE="class: grid, width: 359"]
<TBODY>[TR]
[TD]ROW A[/TD]
[TD]row b[/TD]
[/TR]
[TR]
[TD]Abbott</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AbbVie</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Accenture</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AECOM (CA)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Agilent (CA)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AIG</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Air Liquide</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alliance Data (TX)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Allstate </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amdocs</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Anixter (IL)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aon</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apollo Group (AZ)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Autodesk (CA)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Baker Hughes (TX)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Baxter</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bechtel</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bhpbilliton (TX)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BMC Software (TX)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Boeing</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Boston Consulting Group</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BP</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bridgestone/Firestone (TN)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brown Forman (KY)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cardinal Health</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CareFusion (CA)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cargill</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cargotec Corporation (OH)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Caterpillar</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CGI</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CH2M Hill</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chevron</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chrysler Group</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ConAgra </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ConocoPhillips </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dell</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dow Chemical </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eaton Corporation (OH)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eli Lilly</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Energizer</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ericsson</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Exiro - Techint</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ExxonMobil </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ford Motor Company</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]General Motors</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GenOn Energy (TX)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Google</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grainger (IL)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grant Thorton</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Halliburton</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HNI Corp (IA)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Honda (CA)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HP</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Intel Corporation</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invensys (TX)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Koch Industries (KS)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kraft Foods Group Inc.</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Marathon Oil Corporation</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Marathon Petroleum</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]McKesson</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Medtronic Inc.</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Microsoft Corporation</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Microtek</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monsanto (MO)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Motorola Mobililty</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Motorola Solutions</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]National Oilwell Vargo </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Navigant Consulting</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Navistar International Corp (IL)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nestle </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NetJets</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nokia</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Northwestern Mutual</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Novell (UT)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oracle </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Philips</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phillips 66 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Principal Financial T&T (IA)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Proctor and Gamble </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reed Elsevier Group PLC </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rolls Royce</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales Force</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SAP (CA)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Schlumberger</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sears Holdings Company</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shell</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sonos</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spectra Energy (TX)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sprint</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Starbucks </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Symantec </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Teradata (OH)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toshiba </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USG </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WalMart</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wartsilla (TX)</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wells Fargo </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YAHOO </SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]
THis is Sheet 2 or "HOTEL ACCOUNTS"

[TABLE="class: grid, width: 305"]
<TBODY>[TR]
[TD]ATLANTA</SPAN>[/TD]
[TD]ATLANTA PROSPECTS</SPAN>[/TD]
[/TR]
[TR]
[TD]Accenture Special Project</SPAN>[/TD]
[TD]AGCO</SPAN>[/TD]
[/TR]
[TR]
[TD]Ally/GMAC</SPAN>[/TD]
[TD]Ericsson</SPAN>[/TD]
[/TR]
[TR]
[TD]Assurant</SPAN>[/TD]
[TD]HD Supply</SPAN>[/TD]
[/TR]
[TR]
[TD]Canon USA</SPAN>[/TD]
[TD]KIA</SPAN>[/TD]
[/TR]
[TR]
[TD]Cisco</SPAN>[/TD]
[TD]Koch</SPAN>[/TD]
[/TR]
[TR]
[TD]Costco</SPAN>[/TD]
[TD]Medtronic</SPAN>[/TD]
[/TR]
[TR]
[TD]Deutz</SPAN>[/TD]
[TD]Nestle </SPAN>[/TD]
[/TR]
[TR]
[TD]EMC</SPAN>[/TD]
[TD]SAIA</SPAN>[/TD]
[/TR]
[TR]
[TD]General Dynamics</SPAN>[/TD]
[TD]SED International</SPAN>[/TD]
[/TR]
[TR]
[TD]Given Imaging</SPAN>[/TD]
[TD]Siemens</SPAN>[/TD]
[/TR]
[TR]
[TD]Houghton Mifflin</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Liberty Mutual</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LSI</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Macys</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]McKesson</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Merial/Sanofi-Aventis</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mitsubishi</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NCR</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Primerica</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ricoh</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RockTenn</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rockwell Collins</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UHS Pruitt</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VIASAT</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]

 

[Andrew Poulsom]

It looks like you need:

=IF(COUNTIF('HOTEL ACCOUNTS'!A2:A25,A45),"IN",IF(COUNTIF('HOTEL ACCOUNTS'!B2:B25,A45),"T",""))

 

[esnelson426]

should I create a "key" for IN and T?