| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with multiple values for a lookup
Bob,
Thank you so much. "Bob Phillips" wrote: [VBAX.xls] is the workbook, so replace that with yours Sheet1 is the sheet within that workbook, so replace that Columns A, C and D are columns within that sheet with the freight charges, locations and product lists so replace them Then all it does is compared the target location against the locations list, the target product against the products list, and where both match you get a row number which is used to index into the freight charges. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sal" wrote in message ... Hi Bob, Appreciate your answer but I am going to show my total ignorance now.....Can you explain a wee bit about what the formula below is doing and which bits relate to the 2nd workbook of mine where the freight values are stored. The 2nd workbook is laid out eg Location Prod 1 Prod 2 Prod 3 Prod 4 etc Chch $1.0 $1.50 $3.00 $2.00 etc I am a beginner at this so please excuse all the questions! Thanks "Bob Phillips" wrote: =INDEX([VBAX.xls]Sheet1!$A$2:$A$20,MATCH(1,(D1=[VBAX.xls]Sheet1!$C$2:$C$20)*(E1=[VBAX.xls]Sheet1!$D$2:$D$20),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sal" wrote in message ... Hi there, Apologies if this is a repeat but I cant find my first entry on the discussion site. Problem: I have 2 workbooks. The first contains daily sales figures and I need to look up freight charges stored on workbook 2 based on 1. Location and 2. Product type. Workbook One looks somthing like Location Prod 1 Prod 2 Prod 3 Prod 4 etc Freight Auckland 10 Value determined by Loc & Prod ChCh 60 Etc Locations held in column D and products in E:N I have unsuccesfully tried Index and Match as not really sure what I am doing. Can anyone please help? Can email examples if required. Many thanks for your time. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LOOKUP MULTIPLE VALUES | Excel Discussion (Misc queries) | |||
| Lookup on multiple values | Excel Discussion (Misc queries) | |||
| Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
| lookup multiple values | Excel Worksheet Functions | |||
| how do I lookup multiple values | Excel Discussion (Misc queries) | |||
Threaded Mode
