Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple column lookups
I need to get a formula for Col D in sheet 1 to tell me that for column A in
Sheet 1 Col B does have a matching TYPE in Sheet 2. And then, once that is established (or fixed), I need a formula for Col E which would test both Col A & Col B and confirm that Col C (Cost) matches. Sheet 1 Col A Col B Col C Col D Col E Trip Type Cost A Single $100 Yes $ok A Quad $400 Yes $ok B Super $200 NO C Double $300 Yes $NO Sheet 2 Col A Col B Col C Trip Type Cost A Single $100 A Double $200 A Triple $300 A Quad $400 B Single $100 B Double $200 B Triple $300 B Quad $400 C Single $100 C Double $200 C Triple $300 C Quad $400 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple column lookups
First, create a new column on Sheet2 in D, filling with:
=A2&CHAR(1)&B2 Copy down to end of data in A, In Sheet1, D2 copied down: =IF(COUNTIF(Sheet2!D:D,A2&CHAR(1)&B2),"Yes","No") In Sheet1, E2 copied down: =IF(D2="Yes",IF(INDEX(Sheet2!C:C,MATCH(A2&CHAR(1)& B2,Sheet2!D:D,0))=C2,"$OK","$No"),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple column lookups | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
generate multiple rows based on cell value | Excel Worksheet Functions |