Lookup question

[wibni]

Hello,

I have the below table in Excel 2003.

Is it possible to create the entries under 'Site' in column I with a lookup?
I basically need an array with the values from B1:I2.
If one of the numbers has been found it should return the string from column A.
I tried VLookkup and HLookup but couldn't get it to work.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	GGM	008	028	038	055	066	073
2	NMM	005	011	021	024	032	042	048	049
3
4
5								RigSite	Site
6								005	NMM
7								008	GGM
8								011	NMM
9								021	NMM
10								024	NMM
11								028	GGM
12								032	NMM
13								038	GGM
14								042	NMM
15								048	NMM
16								049	NMM
17								055	GGM
18								066	GGM
19								073	GGM
20								008	GGM
21								028	GGM
Sheet1

 

[Expiry]

Rather than Vlookup, try IF - If it's in row 1, then give me A1, if it's in row 2, give me A2.

=IF(COUNTIF($1:$1,H7),$A$1,IF(COUNTIF($2:$2,H7),$A$2,""))

 

[TiaXL]

Hello,

I have the below table in Excel 2003.

Is it possible to create the entries under 'Site' in column I with a lookup?
I basically need an array with the values from B1:I2.
If one of the numbers has been found it should return the string from column A.
I tried VLookkup and HLookup but couldn't get it to work.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	GGM	008	028	038	055	066	073
2	NMM	005	011	021	024	032	042	048	049
3
4
5								RigSite	Site
6								005	NMM
7								008	GGM
8								011	NMM
9								021	NMM
10								024	NMM
11								028	GGM
12								032	NMM
13								038	GGM
14								042	NMM
15								048	NMM
16								049	NMM
17								055	GGM
18								066	GGM
19								073	GGM
20								008	GGM
21								028	GGM
Sheet1

create your array somewhere else either in hidden columns or a hidden sheet, ensure that the data you want to "lookup" is the on in the first left hand column for ex

008 GGM
028 GGM
038 GGM


then in cell I6 use formula "=IF(ISERROR(VLOOKUP(I6,AB:AC,2,0)),"",(VLOOKUP(I6,AB:AC,2,0)))

You can then copy this formula down to the bottom of column I, it will only show values when it can find one to display, else it will be blank.

 

[wibni]

Thank you for your replies.
The table is generated by our finance system so I can't create it somewhere else or in hidden columns.

What you suggested Expiry works. Thank you very much for that.
Is it possible to include more sites as well?
For example if I wanted to search through the first 4 rows:
GGM
NMM
MWA
KMC

Do I then just continue creating additional IF statements in the false section of the COUNTIF statement?

=IF(COUNTIF($1:$1,H8),$A$1,IF(COUNTIF($2:$2,H8),$A$2,IF(COUNTIF($3:$3,H8),$A$3,IF(COUNTIF($4:$4,H8),$A$4,""))))