Index Matching against multiple columns

[ajwoods]

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

 

[ajwoods]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Forname	Surname		Harness	Lanyards	Hook	Hook	Helmet	Cabloc	Karabina
2	Carl	Ablet						10161XC5006
3	Richard	Austin		20361299/014	10203DF1221	0210-0397-16-10	0210-0398-16-10	XO3460		10190VA1302
4	Lee	Brown		20358884/034	10047CV0802	0709-0210-6-10	0709-0210-6-10	10161XC4991		10170VA
5	Kevin	Bryant
6	Nick	Calderbank
7	Ricky	Callender
8	Richard	Elliss		20362581/044	10047CQ0618	0210-0282-16-10	0210-0283-16-10	10161XC4990		10170VA1231
9	Nick	Farmer		20361820/043	10098CD0192	0509-0864-2-10	0509-0859-2-10	10161XC4998		10170VA0609
10	Mark	Francis
11	Chris	Franklin
12	Chris	Friend			10047CQ0620	0210-0138-16-10	0210-0140-16-10	10155XI5016		10170VA1252
13	Allan	Graves
14	Karl	Harris
15	Clinton	Holdsworth
16	Les	Hopkins
17	Daniel	Ireland
18	Mark	Jiggins		2035884/033	10047CQ0581	0210-0281-16-10	0210-0284-16-10	10154XI4045	377983/43842	10170VA1247
19	Chris	King						XO1527
20	Scott	Lammass			10047CQ0615	0210-0136-16-10	0210-0285-16-10	10154XI4046	377983/43592	10170VA1241
21	Barry	Laud
22	Aaron	Leggett			10117CD1044	0210-0137-16-10	0210-0139-16-10	10154XI4544	41732	10170VA0410
23	Lee	Mayo		20358884/032	10098CO0030	0709-0315-6-10	0709-0097-6-10	10151XI4528		10170VA0616
Issue Name List

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Manufacturer	Product	Serial No.	Description	Cert no.	Supplier				Where?	Forname	Surname
2						Name	Tel	Postcode
3	MGO	Hooks	0210-0281-16-10		AJW-006-030810	Heightworks	7889 544213	WS6 6BD	1	Issued	#N/A	#N/A
4	MGO	Hooks	0210-0284-16-10		AJW-006-030810	Heightworks	7890 544213	WS6 6BD	1	Issued	#N/A	#N/A
5	MGO	Hooks	0210-0138-16-10		AJW-006-030810	Heightworks	7891 544213	WS6 6BD	1	Issued	#N/A	#N/A
6	MGO	Hooks	0210-0140-16-10		AJW-006-030810	Heightworks	7892 544213	WS6 6BD	1	Issued	#N/A	#N/A
7	MGO	Hooks	0210-0139-16-10		AJW-006-030810	Heightworks	7893 544213	WS6 6BD	1	Issued	#N/A	#N/A
Hooks

 

[p45cal]

Apart from trying solutions in msg#8 there might be another way:

= INDIRECT("'Issue Name List'!A" & MAX(('Issue Name List'!$G$2:$H$2000=C24)*(ROW('Issue Name List'!$G$2:$H$2000))))

but this MUST be Array-Entered. This means you commit the formula to the cell with Ctrl+Shift+Enter, not just Enter.
Once one of these is working, we'll sort out hiding the error if the value is not found in either column.

 

[p45cal]

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.

From msg#11 I note that Hooks are in columns F and G, rather than G and H.

 

[ajwoods]

i'm sure if i had any hair left i'd be pulling it out.

thanks for all your help but it seems as though i am the weak link in all this. seem to be getting no where. tried copying that formula pasting it into the cell and then pressing shift crtl ent and nothing.

confused.com

scrap that, just tried chaanging all the H for F as i seem to be a bit stewed at the moment and HORRAH. Thanks for bearing with me.

 

[mikecroom]

Chris

When you have the 1st issue sorted - it is easy to ignore the N/A and replace with anything you want. Even with Excel 2003. Just mimic the 2007 IFERROR function with the attached VBA code.

Option Explicit
Rem Takes the result of a formula as 1st parameter. If it is not an error then display it, else display 2nd parameter
Rem Based on [URL]http://support.microsoft.com/kb/280094[/URL]
Rem This function mimics the Excel 2007 equivalent
Rem =iferror(formula, failvalue) is equivalent to =if(iserror(formula),failvalue, formula)
 
Function IfError(formula As Variant, show As Variant)
On Error GoTo ErrorHandler
If IsError(formula) Then
    IfError = show
Else
    IfError = formula
End If
Exit Function
ErrorHandler:
Resume Next
End Function

Mike

 

[ajwoods]

okay, got he first bit sorted,

now what am i meant to do with the VBA code? how do i get it to work? i have never dealt with VBA before.

cheers

 

[p45cal]

okay, got he first bit sorted,

now what am i meant to do with the VBA code? how do i get it to work? i have never dealt with VBA before.

cheers

It's all explained in the link within the code (on the line starting: Rem Based on)