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]

Really close now. The formula bar is returning the right result when I put the postcode in however when I inseret the formula and change it to the conditions that it needs, (IE on speperate tab) it comes up with #value! I cannot seem to enter the formula using ctrl+shift+enter either. is this a catylist for it to work. Do ineed to write down the formula in a call to copy over (i did try)

 

[Andrew Poulsom]

You must confirm the formula with Ctrl+Shift+Enter. What is preventing you from doing that? You need to put the formula in a single cell and copy/paste it to the remaining cells.

 

[North for Short]

Andrew I have no idea. I have tried ctrl shift and enter and nothing happens is it my formula which is wrong? I have copied it out as best as I could.

=IF(COUNTIF('Booking in'!J$5:J$500,D$3)>=ROWS(H$7:H7),INDEX('Booking in'!H$5:H$500,SMALL(IF('Booking in'!J$5:J$500=D$3,ROW('Booking in'!J$5:J$500)-ROW('Booking in'!J$5)+1),ROWS(H$7:H7))),"")

 

[North for Short]

Andrew I have no idea. I have tried ctrl shift and enter and nothing happens is it my formula which is wrong? I have copied it out as best as I could.

=IF(COUNTIF('Booking in'!J$5:J$500,D$3)>=ROWS(H$7:H7),INDEX('Booking in'!H$5:H$500,SMALL(IF('Booking in'!J$5:J$500=D$3,ROW('Booking in'!J$5:J$500)-ROW('Booking in'!J$5)+1),ROWS(H$7:H7))),"")

I have managed to crack it. After fiddling about with it, The brackets appeared. (problem is, I can't remember what I had done)! the postcode is the same and the addresses are different. Now I have to match the names to the addresses and I am there. Thank you so very much for your help. It has been invaluable. Hapy New year . Until the next time!