VLOOKUP QUESTION??? - MEETING MULTIPLE CRITERIA

[alanDMS]

working with 2 workbooks "CLient-order dbase" workbook A & "ups sheet" spreadsheet workbook B

in workbook A "CLient-order dbase", woksheet A "TBC DBASE" column K contains zip+4 zip code ie. 11741-5921

ZIP CODE


06811-5171
06070-2220
76710-4601
11501-3194
11576-2127
55313
11530-5738
60712-3014
33140-3278
02446-2201
02748-2022
11530-5725
95682-8440
02446
01550-2564
02467-2182
12078-2135


in workbook A "CLient-order dbase", woksheet A "TBC DBASE" column AF contains UPS Zone ie. 2

UPS ZONE


2
2
7
2
2
6
2
5
6
2
2
2
8
2
2
2
2

I now need to retrieve the SCF # for each of these records

SCF means 1st 3 digits of the zip

Since I have the Zip code, I can get the scf-

In column fy I enter the formula =LEFT(K1,3)

results are now:

068
060
767
115
115
553
115
607
331
024
027
115
956
024
015
024
120

Now in workbook B "UPS Sheet", I have a woksheet called UPS Zone

there are 3 collumns:

A= SCF min
B= SCf max
C= UPS zone

It looks like this:


Dest. ZIP ZONE
TO FROM

004 005 2
006 007 45
008 [1]
009 45
010 013 2
014 3
015 018 2
019 3
020 024 2
025 026 3
027 029 2
030 033 3
034 2
035 043 3
044 4
045 3
046 049 4
050 051 3
052 053 2
054 3
055 2
056 059 3
060 089 2
090 099 -
100 127 2
128 136 3
137 139 2
140 142 3
143 4
144 149 3
150 154 4
155 3
156 4
157 159 3




I am trying to figure out a lookup that will pull column C "UPS ZONE"

If SCF either = scf in Column A "SCF MIN"
or
is greater than & equal to column A "SCF MIN" & less than or equal to the Collumn B "SCF MAX"


Now I know if I delete the Max & Min and just list every possible scf straight down in Column A & in Column b just have the zone - I can use a basic vlookup statement. But since I have to find either a match in A or B or between column a or b, I am having trouble.

Thanks again in advance for your help.

Alan

 

[just_jon]

Assumimg hat your data table is sorted ascending on the MIN column, I think you can just use

=INDEX('UPS Sheet!$c:$c,MATCH(TEXT(A1,"000"),'UPS Sheet'!$a:$a))

Where A1 holds the 3-digit of interest.

If it's already text, then skip the TEXT bit and just use A1.

 

[alanDMS]

Hello Jon,

Thankyou for taking the time to respond.

But I am not sure if the formula "=INDEX('UPS Sheet!$c:$c,MATCH(TEXT(A1,"000"),'UPS Sheet'!$a:$a)) " would accomplish what I am looking to do.

On the UPS Sheet
Column A would have a low value
Column B would have a High value (and some cases no value)
Column C would show the zone

I need a formula to look at column A & Column B

And if it Column B is not blank, then greater than or equal to A & Less than or equal to B- Pull C

And if it Column b is blank then equal to A - Pull C

Thanks again,

Alan

 

[just_jon]

Hello Jon,

Thankyou for taking the time to respond.

But I am not sure if the formula "=INDEX('UPS Sheet!$c:$c,MATCH(TEXT(A1,"000"),'UPS Sheet'!$a:$a)) " would accomplish what I am looking to do.

On the UPS Sheet
Column A would have a low value
Column B would have a High value (and some cases no value)
Column C would show the zone

I need a formula to look at column A & Column B

And if it Column B is not blank, then greater than or equal to A & Less than or equal to B- Pull C

And if it Column b is blank then equal to A - Pull C

Thanks again,

Alan

Seems to work, as long as your table is sorted on A ...

Book2
	A	B	C	D	E
1	Min	Max	Zone	Target=	47
2	0	0	Not Found	Zone=	4
3	004	005	2	Target=	7
4	006	007	45	Zone=	45
5	008		1	Target=	34
6	009	045		Zone=	2
7	010	013	2	Target=	22
8	014		3	Zone=	2
9	015	018	2
10	019		3
11	020	024	2
12	025	026	3
13	027	029	2
14	030	033	3
15	034		2
16	035	043	3
17	044		4
18	045		3
19	046	049	4
20	050	051	3
Sheet2

EDIT - Match without a 3rd argument explicitly stated defaults to the

largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

 

[Ginger]

HELP - VLOOKUP

Hi!

Not sure if you can help me out with my inquiry, but look at my q from yesterday if you have the time.
Thanks!

 

[alanDMS]

Jon,

Thank you very much,

I don't completely understand teh logic of that formula, but it does work.

Thank you so much for your time and help.

Alan

 

[alanDMS]

The "=INDEX(C:C,MATCH(E1,A:A))" formula definately seems to work.

Thank you so much.

But I seem to be encountering a data problem.

The data in the database is formated one way & the data in the work book seems to be formated anotherway.

I've tried everything to get them to be the same format (Format>Format Cells>Number) & Paint Brush. But it doesn't seem to work.

Although I know that the number in the cell is in the workbook. It does not make the connection. But if I copy the # from the cell to the database or from the database to the cell in the workbook, then it pulls the appropriate information.

This is what leads me to believe its a formatting issue.

So how can Iget the d-base & the cells in the workbooks to be formatted the same.

Thank you in advance,

Alan

 

[just_jon]

The "=INDEX(C:C,MATCH(E1,A:A))" formula definately seems to work.

Thank you so much.

But I seem to be encountering a data problem.

The data in the database is formated one way & the data in the work book seems to be formated anotherway.

I've tried everything to get them to be the same format (Format>Format Cells>Number) & Paint Brush. But it doesn't seem to work.

Although I know that the number in the cell is in the workbook. It does not make the connection. But if I copy the # from the cell to the database or from the database to the cell in the workbook, then it pulls the appropriate information.

This is what leads me to believe its a formatting issue.

So how can Iget the d-base & the cells in the workbooks to be formatted the same.

Thank you in advance,

Alan

OK, well yes, data types being different is going to cause the formula to fail as is; but, tthat can be fixed.

You could do one of --

Book2
	A	B	C	D	E	F	G
1	Numerics	Text	Blah		Numeric->	009	dd
2	000	000	aa		Text->	000	aa
3	009	000	bb
4	099	000	cc
5	999	000	dd
6
Sheet1

Or change all the data into one type first.

 

[alanDMS]

JUST JON,

THAT WORKED.

THANKYOU VERY MUCH

ALAN