Help with Lookup

jagsdj · May 9, 2002

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 · May 9, 2002

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 · May 9, 2002

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 · May 20, 2002

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 · May 20, 2002

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:

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0><TR><TD COLSPAN=5 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - aaRetVal jagsdj.xls</TD><TD ALIGN=RIGHT>___Running: xl2000 : OS = Windows (32-bit) NT 5.00</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=5><TABLE BORDER=0><TR><TD COLSPAN=5% ALIGN=CENTER BGCOLOR=White>A1</TD><TD COLSPAN=10% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=85% ALIGN=LEFT BGCOLOR=White>Address</TD></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#969696 ALIGN=Left VALIGN=BOTTOM >Address</TD><TD BGCOLOR=#969696 ALIGN=Left VALIGN=BOTTOM >High</TD><TD BGCOLOR=#969696 ALIGN=Left VALIGN=BOTTOM >Low</TD><TD BGCOLOR=#969696 ALIGN=Left VALIGN=BOTTOM >City</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >SUGAR PIKE RD</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >2350</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >2001</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >WOODSTOCK</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >SUGAR PIKE RD</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >5935</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >2351</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >CANTON</TD></TR><TR><TD COLSPAN=5>Sheet2</TD></TR></TABLE>
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, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you
This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo
</CENTER>

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

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0><TR><TD COLSPAN=5 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - aaRetVal jagsdj.xls</TD><TD ALIGN=RIGHT>___Running: xl2000 : OS = Windows (32-bit) NT 5.00</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=5><TABLE BORDER=0><TR><TD COLSPAN=5% ALIGN=CENTER BGCOLOR=White>A1</TD><TD COLSPAN=10% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=85% ALIGN=LEFT BGCOLOR=White>SNO</TD></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >SNO</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Street</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >City</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >2097</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >SUGAR PIKE RD</TD><TD BGCOLOR=#FFCC00 ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript: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</A></TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >5450</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >SUGAR PIKE RD</TD><TD BGCOLOR=#FFCC00 ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript: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</A></TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >2000</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >KING'S RD</TD><TD BGCOLOR=#FFCC00 ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript: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</A></TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD COLSPAN=5>Sheet1</TD></TR></TABLE>
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, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you
This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo
</CENTER>

Is this not what you wanted to have?

Aladin

jagsdj · May 20, 2002

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 · May 20, 2002

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 · May 20, 2002

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 · May 20, 2002

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 · May 28, 2002

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

Help with Lookup

jagsdj

New Member

Excel Facts

Yogi Anand

MrExcel MVP

Aladin Akyurek

MrExcel MVP

jagsdj

New Member

Aladin Akyurek

MrExcel MVP

jagsdj

New Member

Aladin Akyurek

MrExcel MVP

jagsdj

New Member

jagsdj

New Member

jagsdj

New Member

Similar threads

Forum statistics

Share this page

Help with Lookup

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock