Mr Excel & excelisfun Trick 36: VLOOKUP w Approximate Match & Unsorted Table

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

I tried using vlookup with approximate match and my vlookups werent working. I have to lookup a column which is a concatenate of two other columns. does vlookups on approximate match not work with concatenates?

I apologize if this is not right place to ask this question. Also i went through couple of existing thread on MATCH/INDEX, but doesn't seem to solve my problem.

Here is my sample data

Test A Test B Test C Result
1 1 1 A
1 2 2 B
2 1 1 c
2 2 2 D


Based on input values like Test A=1, Test B=2, Test C=1, I would like to see either A or B which is almost nearer since the actual match doesnt exists. I tried this formula, but guess something is wrong with it.

=INDEX(D1:D5,MATCH(1,(A1:A5="1")*(B1:B5="2")*(C1:C5="1"),1))
or
=INDEX(D1:D5,MATCH(1,(A1:A5="1")*(B1:B5="2")*(C1:C5="1"),-1))

It would be great, if someone can help me out in getting the result.

Thanks for your time

I am using RANK but because the values in the Range are calculated the number that I am trying to determine the rank of does not match.

Is there a way of doing an approximate match using the rank function.

I tried FLOOR but that did not eliminate my problem.

Thanks, Michael

I am using an INDEX and MATCH formula that allows me to look in a table of columnar data and retrieve a third value from another column, depending on where the two values in the MATCH function intersect. Given Column A, B, and C contain lookup data and Columns D & E contain the data I am looking to find, I want to return a value from Column C where they intersect (from MSDN example 214142, rev 5.2):

{INDEX($C$2:$C$6,MATCH(D2&E2,$A$2:$A$6&$B$2:$B$6,0))}

Dear all,

I have been trying to use vlookup to fin an approximate value and return the value in another column and keep getting NA error.

I have a range A1:A20 with numbers ranging from 0 to 1 and a range B1:B20 with sales. What I want to do is having in another part of the spreadsheet a list of values to lookup for example 0.2, 0.4, 0.6 and use the formula to look it up en A1:A20 and return the values en B1:b20 with an approximate match.

I have read it can be done combining Index and Match functions but I don't know how.

Thanks.

I have a largeish set of data that is read only (500 x 50). The data represents tests that must be done monthly. How do I move the data into a chart to the right, where each field will find the appropriate number for that unit that was found for each month?

I have tried concatenation, but ran into issues since the month lookup is approximate. I'm past my level of formula understanding here, and am not so much looking for solutions but advice on what sort of topics to learn about.

Here is a simplified example of the data, with the data source on the left in blue and target on the right in yellow:

Date Unit Test Value Unit 1/4/2010 a 10 Month a b c d e f g 1/8/2010 g 11 1/1/2010 1/11/2010 c 12 2/1/2010 1/14/2010 b 13 3/1/2010 1/16/2010 e 14 1/20/2010 d 15 1/23/2010 f 16 2/7/2010 d 17 2/9/2010 c 18 2/12/2010 b 19 2/14/2010 e 20 2/17/2010 a 21 2/19/2010 g 22 2/21/2010 f 23 3/5/2010 a 24 3/6/2010 b 25 3/7/2010 c 26 3/8/2010 d 27 3/9/2010 e 28 3/10/2010 f 29 3/11/2010 g 30


To clarify, although it is not shown in the example data, multiple tests may be done on one day, but there should only be one test per unit for each calendar month.

If I have two columns of data in two different worksheets.

For example:

Sheet 1
$2.40
$2.60
$2.80
$3.00
$3.20
$3.40
$3.60
$3.80

Sheet 2
$3.78
$2.95
$2.54

What formula do I need to create either in visual basic or excel that will enable me to find the value in say Sheet 2 and match it with the closest value in sheet 1. I am hoping to create a formula that will make the data from sheet 2 automatically appear in sheet 1 in the cell next to the closest matching value in sheet 1.

The data would then look as follows:

$2.40
$2.60 $2.54
$2.80
$3.00 $2.95
$3.20
$3.40
$3.60
$3.80 $3.78

Hello, ANYONE

Here's my problem

i want to do this: MATCH(<int(now()),$P$3:$P$1048,0)

Now it doesn't work , and I can't find a way of doing it.

I already use a variation on this that works fine but Excel doesn't like the <

Anyone got any ideas?