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

esnelson426

New Member
Joined
Sep 25, 2013
Messages
7
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) & ")")
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Welcome to MrExcel.

What do you want to match, and in which ranges (you only have one lookup range in your formula)?
 
Upvote 0
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
 
Upvote 0
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??
 
Upvote 0
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",""))
 
Upvote 0
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....
 
Upvote 0
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

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

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>
THis is Sheet 2 or "HOTEL ACCOUNTS"

ATLANTA</SPAN>ATLANTA PROSPECTS</SPAN>
Accenture Special Project</SPAN>AGCO</SPAN>
Ally/GMAC</SPAN>Ericsson</SPAN>
Assurant</SPAN>HD Supply</SPAN>
Canon USA</SPAN>KIA</SPAN>
Cisco</SPAN>Koch</SPAN>
Costco</SPAN>Medtronic</SPAN>
Deutz</SPAN>Nestle </SPAN>
EMC</SPAN>SAIA</SPAN>
General Dynamics</SPAN>SED International</SPAN>
Given Imaging</SPAN>Siemens</SPAN>
Houghton Mifflin</SPAN>
Liberty Mutual</SPAN>
LSI</SPAN>
Macys</SPAN>
McKesson</SPAN>
Merial/Sanofi-Aventis</SPAN>
Mitsubishi</SPAN>
NCR</SPAN>
Primerica</SPAN>
Ricoh</SPAN>
RockTenn</SPAN>
Rockwell Collins</SPAN>
UHS Pruitt</SPAN>
VIASAT</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>
 
Upvote 0
It looks like you need:

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

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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