HELP With search

[David Joseph]

I am a severely disabled veteran and seem to need some help. Ran into a
snag....

in a work book with two pages. page 1 is price creator page 2 is zip code
data..

In the creator page the zip code to (cell a1) and zip code from (cell b1)
are 2 different zip codes of course and
mileage is (cell c1) and (cell d1) is the rate.
33609 33610 35

In the mileage box. the miles needs to be entered from the zip code page.

33602 33603 12
33611 33635 23
33609 33610 35
once this mileage is entered then the rates are auto produced.

I have tried my best to figure it out and get the 35 lets say to the mileage
box into the creator page (Cell c1) but can't get it.

is it also possible to search and find out the result even if it searches
for the result visa/versa
(33602 to 33609 or 33609 to 33602)
and get the same result. would save a lot of data entry.

could you help please!!!! and email at (e-mail address removed)

thanks
David Poe

 

[Tom Ogilvy]

Assume the "mileage box" is on a sheet named mileage with the data in
A1:C2000. In C1 of the creator page:

=Sumproduct(-(Mileage!$A$1:$A$2000=A1),-(Mileage!$B$1:$B$2000=B1),Mileage!$C
$1:$C$2000)

if the zips may be reversed

=Max(Sumproduct(-(Mileage!$A$1:$A$2000=A1),-(Mileage!$B$1:$B$2000=B1),Mileag
e!$C$1:$C$2000),Sumproduct(-(Mileage!$A$1:$A$2000=B1),-(Mileage!$B$1:$B$2000
=A1),Mileage!$C$1:$C$2000))

 

[Bob Phillips]

Hi David,

First things first.

Assuming the data for zip codes is on a worksheet called zip code, cells
A1:C10

=IF(ISNA(MATCH(A1&B1,'zip code'!A1:A10&'zip code'!B1:B10,0)),"No
match",INDEX('zip code'!C1:C10,MATCH(A1&B1,'zip code'!A1:A10&'zip
code'!B1:B10,0),1))

To make it vice versa

=IF(NOT(ISNA(MATCH(A1&B1,'zip code'!A1:A10&'zip code'!B1:B10,0))),INDEX('zip
code'!C1:C10,MATCH(A1&B1,'zip code'!A1:A10&'zip
code'!B1:B10,0),1),IF(NOT(ISNA(MATCH(B1&A1,'zip code'!A1:A10&'zip
code'!B1:B10,0))),INDEX('zip code'!C1:C10,MATCH(B1&A1,'zip code'!A1:A10&'zip
code'!B1:B10,0),1),"No match"))

Both of these are array formulae, so commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David Poe]

Mr. Ogilvy,

I have tried this formula and when I implament the exact cells it is
still not working.

Can I send you my workbook so you can see just what I am trying to
accomplish.

realy need help..
David Poe

Thanks..

"REMEMBER THE PRICE OF FREEDOM IS NOT FREE"