index and match

Hi Guys, Having trouble writing a power query function to accomplish fuzzy matching/lookup. Would really appreciate any help. Here's the scenario: Lookup table: This table contains the SQL like operator (I think this can be used in M-code. In Excel I would use *)...

Is there a way I can modify this Index Match formula, or use a different formula, so that I can reference by column/field names instead of clicking on specific column references?: =INDEX(source!E:E,MATCH(A2,source!D:D,0)) Instead of source!E:E I'd like to find "DESCRIPTION" in row1 of the...

I Have an extensive spreadsheet, with very long text cells: Content Spreadsheet with cells like: (about 20.000 lines) 1. Complete/Long Manufacturer Name 2. Complete/Long Product Full Description 3. Lookup columns to be added: [*=2]Part Number [*=2]Type of Product [*=2]Classification...

Scenario: Within same workbook, two tabs of data that need to be extracted to a third tab. Tab 3 - Master file 35K rows Tab 2 - Will very monthly Tab 1 - end result What I would like to do is: Using Tab 2, compare to Tab 3 master, if column B is equal in both tabs, then I would like to see...

Hi this is my formula {=INDEX('All Players'!B2:B500,MATCH("PG"&'All Players'!B2,'All Players'!A2:A500&'All Players'!B2:B500,0))} It returns the information but in between each correct cell I get #n/a I'm moving all "PG" from one sheet name all players to a second sheet named PG. My formula...

<tbody> Cubic Local $ Outer Area $ ShortHaul $ Longhaul $ InterIsland $ 0.003 2.00 4.00 6.00 8.00 10.00 0.005 2.20 4.40 6.60 8.80 11.00 0.008 2.40 4.80 7.20 9.60 12.00 0.01 2.60 5.20 7.80 10.40 13.00 0.012 2.80 5.60 8.40 11.20 14.00 0.016 3.00 6.00 9.00 12.00 15.00 0.02 3.20 6.40...

Hi, I have the following list of data:- <tbody> A B 7 750<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Verdana}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Verdana}table.t1 {border-collapse: collapse}td.td1 {border-style: solid...

I am trying to index/match/lookup values from a Quickbooks report that appear on another sheet in two separate columns and on different rows that will return a value from a 3rd column. I have exported a report from Quickbooks that totals based on Class and Sub-Class. I have created a...

Hello, please help. I am guessing I can use index and match here, but for some reason I am not getting the right formula. Here is my data I need to find the Max number on the bottom row and once I have the max number on that row, I need the corresponding value of the cell that is in the top row...

Hi I've been trying to use index match match in vba and I just can't get it right. I've tried a couple of ways. The 1st thing I tried was: Me.Controls("POinv" & x).Value = Application.Index(wsP.Range("B:L"), Application.Match(Me.Inv2.Value, wsP.Range("K:K"), 0), Application.Match(wsP.Cells(3...

Hi guys, I'm in a pickle for a few days now. My problem just wont go away and I've wasted few days on trying to solve it. I'm ready for some help since I hit dead end. Let me be as simple as I can. Sheet 1 <colgroup><col><col span="4"></colgroup><tbody> CUSTOMER RATE 1 RATE 2 RATE 3 RATE 4...

I have ten columns. The first column (column A) is the sales reps' names. Columns B thru J are different codes referencing order # by clients. I need to look up the order # in columns B thru J and know the sales rep who completed the order. I know Index Match will work but am having problems...

I'm not sure what to call the function I'm looking for, not even positive multiplying by a matrix is the correct name for what I want to do, but I'll describe below. I have a table similar to this: <tbody> (Cell: A1) Components> CompA CompB CompC Products Demand Prod1 500 1 2 Prod2...

<tbody> Help!!! I need a formula for this case: In sheet 1, there are two columns A and B. If a number is inserted in column B, the matching projectno. in column A needs to be transferred to sheet 2. Blank cells has to be skipped so there will be a list of projectno. which will activate if the...

Hello, My query is that I would like to use Index and Match functions that relate to the Date and Code criteria as per query below. It can be assumed that the data box below starts from cell A1 to C8 and the criteria box from E1 to G8. The formula I have been using is the following...

How to return multiple values in vba? What to use index or any other function

Hi, I'm creating a sheet that ranks vendors by cost. I have a table below the cost/ranking table where I want to display the preferred carrier name. I'm using index and match in this formula, but it isn't updating as filters are adjusted. For example: If the Equip Type is filtered to "B"...

I have a table that looks like the one below with the dates sorted in descending order. Person Date Number Jose 1/1/2014 98 Rosa 5/14/14 47 Jose 6/1/14 3 Rosa 2/1/15 456 Jose 12/5/15 3 Rosa 5/2/16 -10 I want to enter a name and date and have excel search...

Hi, I've the following columns : <tbody> Segment 1 Segment 2 Segment 3 Segment 4 Segment 5 Segment 6 Segment 7 X FALSE FALSE FALSE FALSE FALSE FALSE X FALSE FALSE FALSE FALSE FALSE FALSE FALSE Y FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Y FALSE FALSE FALSE FALSE FALSE FALSE...

Hi all, As with the screenshots below I am struggling (in my head) to figure out a way to turn my 'Product!$AK$31:$IQ$31>0' & 'Product!$AK$29:$IQ$29' into a dynamic range (PIC1) i.e. I want to be able to insert a Match formula to reference cell AE57 - 'Panel_1' & "Product Description" which...