Help with Look up reference

N

North for Short

New Member
Joined
Nov 17, 2010
Messages
46
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 20131 1MOUSE48 DISNEY GARDENSZ12345NO
14 December 20131 2WATCHINGS108 ALEXANDRA ROADZ23456NO
14 December 20131 3TRAVISS12 OLDWOOD CHASEZ34566YES24 December 2013
14 December 20131 4SMITHY14 COTTESMORE PLACEZ44433NO
14 December 20131 5JONESEY7 SALISBURY ROADZ33433NO

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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Does that mean I nwed to an index in? The spreadsheet is based on what product has not been delivered and therefore brought baclk to the depot
 
Upvote 0
If you don't want to sort your lookup table you can use:

=INDEX('Booking in'!G5:G500,MATCH(D3,'Booking in'!H5:H500,FALSE))
 
Upvote 0
It works for me:


Excel 2010
ABCDEFGHI
3Z4443314 COTTESMORE PLACE
4
514-Dec-1311MOUSE48 DISNEY GARDENSZ12345NO
614-Dec-1312WATCHINGS108 ALEXANDRA ROADZ23456NO
714-Dec-1313TRAVISS12 OLDWOOD CHASEZ34566YES
814-Dec-1314SMITHY14 COTTESMORE PLACEZ44433NO
914-Dec-1315JONESEY7 SALISBURY ROADZ33433NO
Booking in
Cell Formulas
RangeFormula
G3=INDEX('Booking in'!G5:G500,MATCH(D3,'Booking in'!H5:H500,FALSE))
 
Upvote 0
:ROFLMAO:Yep, When you put it like that, works a treat. Thank you very much. I was trying to base it around the lookup and post code but this is even better. thanks a lot:ROFLMAO:
 
Upvote 0
North for Short said:
:ROFLMAO:Yep, When you put it like that, works a treat. Thank you very much. I was trying to base it around the lookup and post code but this is even better. thanks a lot:ROFLMAO:
Click to expand...

Another quandry, what happens if there is more than one customer on the postcode for instance 7 salisbury and 19 salisbury have the same postcode z33433, is there a code so I can seprate them?
 
Upvote 0
Formula in G1 copied down:

Book1
ABCDEFGHI
1Z23456108 ALEXANDRA ROAD
212 OLDWOOD CHASE
3
4
514-Dec-1311MOUSE48 DISNEY GARDENSZ12345NO
614-Dec-1312WATCHINGS108 ALEXANDRA ROADZ23456NO
714-Dec-1313TRAVISS12 OLDWOOD CHASEZ23456YES
814-Dec-1314SMITHY14 COTTESMORE PLACEZ44433NO
914-Dec-1315JONESEY7 SALISBURY ROADZ33433NO
Booking in
Cell Formulas
RangeFormula
G1:G2G1=IF(COUNTIF('Booking in'!H$5:H$500,D$1)>=ROWS(G$1:G1),INDEX('Booking in'!G$5:G$500,SMALL(IF('Booking in'!H$5:H$500=D$1,ROW('Booking in'!H$5:H$500)-ROW('Booking in'!H$5)+1),ROWS(G$1:G1))),"")
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
860
AlanY
A
N
  • Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
L
  • Question
VBA - Consolidate data and Sum mulitiple ranges
Replies
1
Views
1K
MickG
M
D
  • Question
Complex Bullet POint Addition
Replies
1
Views
495
DRSteele
DRSteele
S
  • Question
Formula Help
Replies
4
Views
521
Fluff
Fluff

Forum statistics

Threads
1,214,892
Messages
6,122,112
Members
449,066
Latest member
Andyg666

Share this page

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