Help with "double" search (lookup).

  • Thread starter Thread starter Loranga
  • Start date Start date
L

Loranga

Aloha!
I have a woorksheet with about 650 rows. It contains destination
hauliers and index.
I was woundering if its possible to do a search where I only fill i
the destination and the haulier and the result I would like is th
index.
This is an example:
(destinaton is Colum A, Haulier colum B and Index colum C)


Code
-------------------
Destination Haulier Index
Stockholm DHL 100
Stockholm Schenker 105
Stockholm DFDS 107
Stockholm Geol 110
Gothenburg DFDS 100
Gothenburg DHL 117
Gothenburg Schenker 120

Search:
Stockholm DFDS 10
 
Hi
if you type thos two values in cell E1 and F1 try the
following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100=E1)*(B1:B100=F1),0))
 
Let A1:C8 house the sample you provided, including the labels.

In D2 enter & copy down:

=A2&CHAR(127)&B2

Then use the following ordinary formula for retrieving an Index value...

=INDEX($C$2:$C$8,MATCH(E2&CHAR(127)&F2,$D$2:$D$8,0))

where E2 houses a Destination value like Stockholm and F2 a Haulier value
like DFDS.
 
Back
Top