Help with Look up reference

[North for Short]

Hi,

A merry Christmas to you all. Haven't been here for a while and the OS systems have evolved. I am looking for a solution to help me look up a reference such as a zip code and then to find the address. I have a spreadsheet with customer's addresses on it and I need to find a particular addreess, I type in the zip code (post code in UK) and the address should appear. What is happening is that the address does not match the zip code . From that I have another lookup reference to check on a customer's name, which should match up the addrress.

The function I am using and have done so before is a simple look up however the addresses are not corresponding to the zipcode

EG =LOOKUP(D3,'Booking in'!H5:H300,'Booking in'!G5:G500)

I have inserted a simple table with data. Can someone tell me what I have missed?

14 December 2013	1			1	MOUSE	48 DISNEY GARDENS	Z12345	NO
14 December 2013	1			2	WATCHINGS	108 ALEXANDRA ROAD	Z23456	NO
14 December 2013	1			3	TRAVISS	12 OLDWOOD CHASE	Z34566	YES	24 December 2013
14 December 2013	1			4	SMITHY	14 COTTESMORE PLACE	Z44433	NO
14 December 2013	1			5	JONESEY	7 SALISBURY ROAD	Z33433	NO

<COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 4152" width=146><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 540" width=19><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 4266" width=150><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 3328" width=117><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2247" width=79><COL style="WIDTH: 184pt; mso-width-source: userset; mso-width-alt: 6968" width=245><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 12800" width=450><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3043" width=107><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 3896" width=137><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 4209" width=148><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 6001" width=211><TBODY>
</TBODY>

 

[North for Short]

I am having problems with the formula, mainly because the sheet where this formula is, is on another tab and the postcode reference is D3
So my set up is tab 1 booking in, tab two Finder.

 

[Andrew Poulsom]

Can't you just change D$1 to D$3?

 

[North for Short]

I tried that. I am going to have another go but will it make a difference that it is on another sheet?

 

[Andrew Poulsom]

No, the formula I posted already contains references to the "Booking in" sheet.

 

[North for Short]

I am back, I have tried using that formula and to no avail. What I am after is to list any address on that postcode. I can get the individual formula to work no problem but as soon as I try and put in the other one I get value or the wrong address completely. Is there something missing?

Date	Count customers	INDEX	Customer Name	Customer Address	Post Code	Booked in by
14 December 2013	1	1	SPIDERMAN	123 ANY ROAD,	AB12	9XZ
14 December 2013	1	2	WOLVERINE	5 HOLLYWOOD	AB12	1AB
14 December 2013	1	3	WONDERWOMAN	2 NEW YORK SQ	AB12	0LS
14 December 2013	1	4	BATMAN	1 WAYNE MANSIONS	AB12	9XZ
14 December 2013	1	5	SUPERMAN	THE FARM	AB12	7AN
14 December 2013	1	6	ROBIN	1A WAYNE MANSIONS	AB12	9XZ

<TBODY>
</TBODY>

This what my booking in form looks like. underneath is my tracker tab and this is where I have the trouble

Enter Postcode</SPAN>	AB12</SPAN>	9xz</SPAN>		#VALUE!</SPAN>
				Address</SPAN>
				#VALUE!</SPAN>
				#VALUE!</SPAN>
				1A WAYNE MANSIONS</SPAN>
On one address the original formula works but the new formula I cannot get going. So close now!

<TBODY>
</TBODY>

 

[Andrew Poulsom]

Do you have the Postcode in two columns there?

 

[CheryBTL]

Try this on:
=INDEX('Booking in'!G$5:G$500,MATCH(D3,'Booking in'!H$5:H$500,FALSE))

More simplified
=INDEX('Booking in'!G:G,MATCH(D3,'Booking in'!H:H,))
or
=VLOOKUP(D3,'Booking in'!G:H,2,)

 

[North for Short]

I am only using the last three digits

 

[North for Short]

Tried that, didn't work, I was wondering whether instead of the postcode I try and simplyfy it by using the name.

 

[Andrew Poulsom]

Formula in I1 copied down:

Book1
	A	B	C	D	E	F	G	H	I
1								9XZ	123 ANY ROAD,
2									1 WAYNE MANSIONS
3									1A WAYNE MANSIONS
4	Date	Count customers	INDEX	Customer Name	Customer Address	Post Code
5	14-Dec-13	1	1	SPIDERMAN	123 ANY ROAD,	AB12	9XZ
6	14-Dec-13	1	2	WOLVERINE	5 HOLLYWOOD	AB12	1AB
7	14-Dec-13	1	3	WONDERWOMAN	2 NEW YORK SQ	AB12	0LS
8	14-Dec-13	1	4	BATMAN	1 WAYNE MANSIONS	AB12	9XZ
9	14-Dec-13	1	5	SUPERMAN	THE FARM	AB12	7AN
10	14-Dec-13	1	6	ROBIN	1A WAYNE MANSIONS	AB12	9XZ
Booking in

Cell Formulas
Range	Formula
I1:I3	I1	=IF(COUNTIF('Booking in'!G$5:G$500,H$1)>=ROWS(I$1:I1),INDEX('Booking in'!E$5:E$500,SMALL(IF('Booking in'!G$5:G$500=H$1,ROW('Booking in'!G$5:G$500)-ROW('Booking in'!G$5)+1),ROWS(I$1:I1))),"")