Index Matching against multiple columns

ajwoods

New Member
Joined
Nov 19, 2010
Messages
8
Hi there,
I am a relative novice at all this, so please bear with me. I have a spread sheet where i want to check a serial number against 2 possible columns that it would be in and then return the name of the person it has been issed to. At the moment i have it sorted that it will look in one column but can't get it to look at the other.

This is my equation:
=INDEX('Issue Name List'!$A$2:$J$2000,MATCH(C24,'Issue Name List'!$H$2:$H$2000,0),1)

I would like it to look it G2:G2000 as well.

Also when on a roll is there any way of stopping it retuning a value of N/A if the serial number isn't there yet and leaving the cell blank?

Thanks in advance
Chris:):)
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,672
Office Version
  1. 2016
Platform
  1. Windows
Hi Chris welcome to the Board,

Microsoft give some examples of using Index and Match with multiple conditions. Here is an example and also a link to there site

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Part</TD><TD>Code</TD><TD>Price</TD><TD>Find Part</TD><TD>Find Code</TD><TD>Result</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>X</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">5</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>X</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">6</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">7</TD><TD>X</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">8</TD><TD>X</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">5</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>{=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))}</TD></TR><TR><TD>F3</TD><TD>{=INDEX($C$2:$C$5,MATCH(D3,IF($B$2:$B$5=E3,$A$2:$A$5),0))}</TD></TR><TR><TD>F4</TD><TD>{=INDEX($C$2:$C$5,MATCH(D4,IF($B$2:$B$5=E4,$A$2:$A$5),0))}</TD></TR><TR><TD>F5</TD><TD>{=INDEX($C$2:$C$5,MATCH(D5,IF($B$2:$B$5=E5,$A$2:$A$5),0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


http://support.microsoft.com/kb/214142

I hope it helps
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
try (untested):
Code:
=INDEX('Issue Name List'!$A$2:$J$2000,IF(ISERROR(MATCH(C24,'Issue Name List'!$H$2:$H$2000,0)),MATCH(C24,'Issue Name List'!$G$2:$G$2000,0),MATCH(C24,'Issue Name List'!$H$2:$H$2000,0)))
Depending on which version of Excel you're using this could be shortened with the later IFERROR function.

re:
is there any way of stopping it retuning a value of N/A if the serial number isn't there yet and leaving the cell blank?
Yes, quite easy in Excel 2007 upwards, easy but lots longer resulting formula in earlier versions. Again this would use the IFERROR function.

Which version are you using?
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763

ADVERTISEMENT

using Excel 2003
Test my answer to the first part of your question first, then post that working/tweaked formula here, then then we'll look at returning a blank if the value is not found in either column.
 

ajwoods

New Member
Joined
Nov 19, 2010
Messages
8
thanks for your responses so far, looking at things not sure if i explained my request well, or if i'm just getting confused.

put that formula in and no go. It seems to be looking to post to G or H columns rather than looking to match what is there and then displaying from A.

have you got an email/twitter address and will email pics of the spreadsheet that may explain thing better. i am aware that i seem to be making no sense. tried posting the pics on here but don't understand how to.

thanks again chris
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,672
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Chris,

You can download Excel Jeanie which is an Add in for Excel and when you use it, it will convert your spreadsheet you copy it to a web page and you paste in the HTML code into your thread and it then will show the spreadsheet in your thread, you can download Excel Jeanie from here.

http://www.excel-jeanie-html.de/index.php?f=1
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
I made a mistake when altering your formula and left out the 1 towards the end. It should be:

Code:
=INDEX('Issue Name List'!$A$2:$J$2000,IF(ISERROR(MATCH(C24,'Issue Name List'!$H$2:$H$2000,0)),MATCH(C24,'Issue Name List'!$G$2:$G$2000,0),MATCH(C24,'Issue Name List'!$H$2:$H$2000,0))[SIZE=4][B][COLOR=Red],1[/COLOR][/B][/SIZE])

Alternatively, it could be:

Code:
=INDEX('Issue Name List'!$A$2:$[SIZE=4][B][COLOR=Red]A[/COLOR][/B][/SIZE]$2000,IF(ISERROR(MATCH(C24,'Issue Name List'!$H$2:$H$2000,0)),MATCH(C24,'Issue Name List'!$G$2:$G$2000,0),MATCH(C24,'Issue Name List'!$H$2:$H$2000,0)))
 

ajwoods

New Member
Joined
Nov 19, 2010
Messages
8
file:///C:/Users/Chris/Documents/EJHTMLe/TempJean.htm

try this link see if it works. if so you will be able to see that on the issue name list there are 2 places that we could enter the issue of a hook.

at the moment on the hooks page it only looks at one of the columns to find the details and then tell me who it has been issued to. it currently works for the others (lanyards, harness etc...) but because it could be in either column it seems to stuff things up.

Chris
 

Watch MrExcel Video

Forum statistics

Threads
1,108,579
Messages
5,523,705
Members
409,531
Latest member
Lmfacc

This Week's Hot Topics

Top