Help with Lookup

[jagsdj]

hello...

I have a Excel Worksheet with some columns and one column is the number portion of an address field, another column is the text portion of the address field. For example, column 1 contains '700' and column 2 contains 'Airport dr'. This is what the worksheet looks like:
Street# Address
700 Airport dr

Here comes the spinner...

I have another worksheet which contains the text field of the address portion and it also contains a 'High' field and a 'Low' Field as well as a column which contians the city name.
So, my second worksheet looks like this:
Address High Low City
Airport dr 100 300 Acworth
Airport dr 500 800 Canton
...

My question is how would I set this up so that based on the information in worksheet1 that I am able to select the second airport dr, since the street# 700, falls within the range of the second record. So, the city would be canton and NOT acworth.
Did that make sense?

I tried to use a VLOOKUP, but to no avail..

All help is GREATLY appreciated!
jags
jagsdj@hotmail.com

[Yogi Anand]

On 2002-05-09 14:47, jagsdj wrote:
hello...

I have a Excel Worksheet with some columns and one column is the number portion of an address field, another column is the text portion of the address field. For example, column 1 contains '700' and column 2 contains 'Airport dr'. This is what the worksheet looks like:
Street# Address
700 Airport dr

Here comes the spinner...

I have another worksheet which contains the text field of the address portion and it also contains a 'High' field and a 'Low' Field as well as a column which contians the city name.
So, my second worksheet looks like this:
Address High Low City
Airport dr 100 300 Acworth
Airport dr 500 800 Canton
...

My question is how would I set this up so that based on the information in worksheet1 that I am able to select the second airport dr, since the street# 700, falls within the range of the second record. So, the city would be canton and NOT acworth.
Did that make sense?

I tried to use a VLOOKUP, but to no avail..

All help is GREATLY appreciated!
jags
jagsdj@hotmail.com

Use
=INDEX(A1:D3,MATCH(700,C1:C3)+1,1)

[Aladin Akyurek]

I'll assume that A1:B2 in Sheet1 has the following:

{"Street#","Address";
700,"Airport dr"}

and that A1:D3 in Sheet2:

{"Address","High","Low","City";
"Airport dr",100,300,"Acworth";
"Airport dr",500,800,"Canton"}

In C1 in Sheet1 enter: City

In C2 enter:

=IF(COUNTIF(Sheet2!A:A,B2),INDEX(Sheet2!D:D,SUMPRODUCT((B2=Sheet2!A2:A3)*(A2>=Sheet2!B2:B3)*(A2<=Sheet2!C2:C3)*(ROW(Sheet2!D2:D3)))),"Not found")

Note. This formula assumes that for a combination of street# and address in Sheet1, there exist either no corrresponding city value or just a single city value in Sheet2. Although checking for the latter condition possible, it would be expensive to do so.

Aladin

[jagsdj]

aladin,

I tried your formulae and it keeps giving me "not found" for all cells. I noticed that ALL references point to Sheet2, shouldn't at least one of the references point to sheet1?

I've tried many variations of the formulae and nothing seems to work. I would REALLY appreciate your help...thanks again.

here is some sample data again:
this is sheet1, SNO = street number
SNO Street City
2097 SUGAR PIKE RD *woodstock*
5450 SUGAR PIKE RD *canton*

sheet2:
Address High Low City
SUGAR PIKE RD 2350 2001 WOODSTOCK
SUGAR PIKE RD 5935 2351 CANTON

the astericks represent what SHOULD be poupulated from sheet2...

thanks again!!
all help is greatly appreciated

[Aladin Akyurek]

On 2002-05-20 12:28, jagsdj wrote:
aladin,

I tried your formulae and it keeps giving me "not found" for all cells. I noticed that ALL references point to Sheet2, shouldn't at least one of the references point to sheet1?

I've tried many variations of the formulae and nothing seems to work. I would REALLY appreciate your help...thanks again.

here is some sample data again:
this is sheet1, SNO = street number
SNO Street City
2097 SUGAR PIKE RD *woodstock*
5450 SUGAR PIKE RD *canton*

sheet2:
Address High Low City
SUGAR PIKE RD 2350 2001 WOODSTOCK
SUGAR PIKE RD 5935 2351 CANTON

the astericks represent what SHOULD be poupulated from sheet2...

thanks again!!
all help is greatly appreciated

Here is Sheet2 where your data is:

Microsoft Excel - aaRetVal jagsdj.xls ___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1 = Address
	A	B	C	D
1	Address	High	Low	City
2	SUGAR PIKE RD	2350	2001	WOODSTOCK
3	SUGAR PIKE RD	5935	2351	CANTON
Sheet2

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.26]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Here is Sheet1 where you want to retrieve the city value for a given SNO and Street:

Microsoft Excel - aaRetVal jagsdj.xls ___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1 = SNO
	A	B	C	D
1	SNO	Street	City
2	2097	SUGAR PIKE RD	:alert('=IF(COUNTIF(Sheet2!A:A,B2),INDEX(Sheet2!D:D,SUMPRODUCT((B2=Sheet2!$A$2:$A$3)*(A2%3E=Sheet2!$B$2:$ B$3)*(A2%3C=Sheet2!$C$2:$C$3)*(ROW(Sheet2!$D$2:$D$3)))),%22Not%20found%22)')>WOODSTOCK
3	5450	SUGAR PIKE RD	:alert('=IF(COUNTIF(Sheet2!A:A,B3),INDEX(Sheet2!D:D,SUMPRODUCT((B3=Sheet2!$A$2:$A$3)*(A3%3E=Sheet2!$B$2:$ B$3)*(A3%3C=Sheet2!$C$2:$C$3)*(ROW(Sheet2!$D$2:$D$3)))),%22Not%20found%22)')>CANTON
4	2000	KING'S RD	:alert('=IF(COUNTIF(Sheet2!A:A,B4),INDEX(Sheet2!D:D,SUMPRODUCT((B4=Sheet2!$A$2:$A$3)*(A4%3E=Sheet2!$B$2:$ B$3)*(A4%3C=Sheet2!$C$2:$C$3)*(ROW(Sheet2!$D$2:$D$3)))),%22Not%20found%22)')>Not found
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.26]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Is this not what you wanted to have?

Aladin

[jagsdj]

yes...

but, all its giving me is "not found" in every row. I double checked the values against sheet2 and there are matches there, but in the row there are 'not found'.

any ideas!

I really do appreciate your time and patience...

thanks again...

jags

[Aladin Akyurek]

On 2002-05-20 13:03, jagsdj wrote:
yes...

but, all its giving me is "not found" in every row. I double checked the values against sheet2 and there are matches there, but in the row there are 'not found'.

any ideas!

I really do appreciate your time and patience...

thanks again...

jags

Your SNO values are maybe differently formatted in Sheet1 and Sheet2. You can check this by applying:

=ISNUMBER(an-SNO-cell)

Another possibility is that the street mames are different because of extra spaces within an around. You can check for that bu using:

=LEN(a-street-cell) in both sheets.

[jagsdj]

aladin,

There's just SOMETHING i'm missing...any other ideas!?!? I just can't get it to work, I do have all the datatypes of the appropriate fields correct and they do match.

any other ideas!
Thanks again!!!


jags

[jagsdj]

the formulae:
IF(COUNTIF(Sheet2!A:A,K2),INDEX(Sheet2!D:D,SUMPRODUCT((K2=Sheet2!$A$2:$A$4062)*(J2>=Sheet2!$B$2:$B$4062)*(J2<=Sheet2!$C$2:$C$4062)*(ROW(Sheet2!$D$2:$D$4062)))),"NOT FOUND")

the 'sumproduct' result is returning a #NUM! error, I don't think that's right...

Again, I really do appreciate your help and patience...

thanks
jags

[jagsdj]

Aladin,

I wanted to thank you for your help...you helped me alot...the trouble was not with your formulae but with the data types of my fields in excel!

THanks again!
jags