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:):)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)))
 
Upvote 0
TempJean.htm


does that work?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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