Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup match 2 items
I want to lookup 2 items for a match to enter 3rd item.
EPP 2.80 x? want A3 to have a lookup function that matches both A1 and A2 to the array. EPS 1.25 0.5% EPS 1.5 0.5% EPS 1.75 0.5% EPS 2 0.5% EPS 2.25 0.5% EPS 2.5 0.5% EPS 3 0.5% Gcet 3 0.5% Gcet 5 0.5% Rmer 1.5 0.5% Rmer 1.75 0.5% Rmer 2 0.5% Rmer 2.25 0.5% Rmer 2.5 0.5% Rmer 3 0.5% Arcel 1.5 1.0% EPP-B 3.75 2.2% EPP-W 3.75 2.2% EPP-B 2.8 2.3% EPP-W 2.8 2.3% EPP-B 1.88 2.5% EPP-W 1.88 2.5% EPE 2 3.0% EPE 3 3.0% EPP-B 1.3 3.0% EPP-W 1.3 3.0% |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup match 2 items
There'll be a clever way, but for me
I'd add a helper column =A1&A2 then lookup on EPP2.80 Steve On Wed, 16 Aug 2006 16:18:01 +0100, HFC-SC wrote: I want to lookup 2 items for a match to enter 3rd item. EPP 2.80 x? want A3 to have a lookup function that matches both A1 and A2 to the array. EPS 1.25 0.5% EPS 1.5 0.5% EPS 1.75 0.5% EPS 2 0.5% EPS 2.25 0.5% EPS 2.5 0.5% EPS 3 0.5% Gcet 3 0.5% Gcet 5 0.5% Rmer 1.5 0.5% Rmer 1.75 0.5% Rmer 2 0.5% Rmer 2.25 0.5% Rmer 2.5 0.5% Rmer 3 0.5% Arcel 1.5 1.0% EPP-B 3.75 2.2% EPP-W 3.75 2.2% EPP-B 2.8 2.3% EPP-W 2.8 2.3% EPP-B 1.88 2.5% EPP-W 1.88 2.5% EPE 2 3.0% EPE 3 3.0% EPP-B 1.3 3.0% EPP-W 1.3 3.0% |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup match 2 items
=INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:a100=A1)* (Sheet2!B1:B100=B1),0))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HFC-SC" wrote in message ... I want to lookup 2 items for a match to enter 3rd item. EPP 2.80 x? want A3 to have a lookup function that matches both A1 and A2 to the array. EPS 1.25 0.5% EPS 1.5 0.5% EPS 1.75 0.5% EPS 2 0.5% EPS 2.25 0.5% EPS 2.5 0.5% EPS 3 0.5% Gcet 3 0.5% Gcet 5 0.5% Rmer 1.5 0.5% Rmer 1.75 0.5% Rmer 2 0.5% Rmer 2.25 0.5% Rmer 2.5 0.5% Rmer 3 0.5% Arcel 1.5 1.0% EPP-B 3.75 2.2% EPP-W 3.75 2.2% EPP-B 2.8 2.3% EPP-W 2.8 2.3% EPP-B 1.88 2.5% EPP-W 1.88 2.5% EPE 2 3.0% EPE 3 3.0% EPP-B 1.3 3.0% EPP-W 1.3 3.0% |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup match 2 items
SteveW thanks this works for me
"SteveW" wrote: There'll be a clever way, but for me I'd add a helper column =A1&A2 then lookup on EPP2.80 Steve On Wed, 16 Aug 2006 16:18:01 +0100, HFC-SC wrote: I want to lookup 2 items for a match to enter 3rd item. EPP 2.80 x? want A3 to have a lookup function that matches both A1 and A2 to the array. EPS 1.25 0.5% EPS 1.5 0.5% EPS 1.75 0.5% EPS 2 0.5% EPS 2.25 0.5% EPS 2.5 0.5% EPS 3 0.5% Gcet 3 0.5% Gcet 5 0.5% Rmer 1.5 0.5% Rmer 1.75 0.5% Rmer 2 0.5% Rmer 2.25 0.5% Rmer 2.5 0.5% Rmer 3 0.5% Arcel 1.5 1.0% EPP-B 3.75 2.2% EPP-W 3.75 2.2% EPP-B 2.8 2.3% EPP-W 2.8 2.3% EPP-B 1.88 2.5% EPP-W 1.88 2.5% EPE 2 3.0% EPE 3 3.0% EPP-B 1.3 3.0% EPP-W 1.3 3.0% |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup match 2 items
is there always a match?
Is it always a Unique answer in each case? Assume the match is further down in the table than your example permits =SUMPRODUCT(--(C1:C100=A1),--(D1:D100=A2),E1:E100) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------- It's easier to beg forgiveness than ask permission :-) "HFC-SC" wrote: I want to lookup 2 items for a match to enter 3rd item. EPP 2.80 x? want A3 to have a lookup function that matches both A1 and A2 to the array. EPS 1.25 0.5% EPS 1.5 0.5% EPS 1.75 0.5% EPS 2 0.5% EPS 2.25 0.5% EPS 2.5 0.5% EPS 3 0.5% Gcet 3 0.5% Gcet 5 0.5% Rmer 1.5 0.5% Rmer 1.75 0.5% Rmer 2 0.5% Rmer 2.25 0.5% Rmer 2.5 0.5% Rmer 3 0.5% Arcel 1.5 1.0% EPP-B 3.75 2.2% EPP-W 3.75 2.2% EPP-B 2.8 2.3% EPP-W 2.8 2.3% EPP-B 1.88 2.5% EPP-W 1.88 2.5% EPE 2 3.0% EPE 3 3.0% EPP-B 1.3 3.0% EPP-W 1.3 3.0% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup nearest value (Index & Match) | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Match function multiple items | Excel Worksheet Functions | |||
Lookup across 2 or more columns to match a row | Excel Worksheet Functions | |||
Lookup Match | Excel Worksheet Functions |