Highline Excel Class 44: Basics of INDEX & MATCH functions

Hi all,

Can anyone help with the following task please.
I need to find a way for excel to auto return and fill the 'Reports To' column, as it is 1000's of rows long and I currently have to do it manualy.

I have changed the names to dumies obviously but the concept is the same. It is company hierachies.

2 3 4 5 6 7 8 9 10 Reports To Mickey Mouse Mickey Mouse Donald Duck Mickey Mouse Mini Mouse Donald Duck Roadrunner Mini Mouse Bugs Bunny Roadrunner Kermit Frog Bugs Bunny Elmo Bugs Bunny David Beckam Bugs Bunny Kermit Frog Bugs Bunny

Not sure if the subject is on the right track or not....

I need to compare two lists in order to be sure the % in list #1 is equal to the Distribution % in list #2. I assume that index/match/lookup or some variation thereof is what I need to use to do this.

List #1
PersonID Position Last CC WBS %
00000014 50117542 Downie 1012003620 (Null) 7.00
00000014 50117542 Downie 1012555060 (Null) 93.00
00004370 50102228 Buxton 1012003620 (Null) 24.00
00004370 50102228 Buxton 1012555060 (Null) 76.00

List #2
Position Funds Center Grant Distribution %
50117542 1012003620 GMNR 7.00
50117542 1012555060 GMNR 93.00
50102228 1012003620 GMNR 24.00
50102228 1012555060 GMNR 76.00

I am not even sure where to start for this type of formula. I know I would have to find the position in list#1 and then lookup the corresponding position in list#2 but I am not sure how to find the right row to make sure the percentages are equal. Each position may have multiple lines that should total to 100% (see Downie with 2 CC that total 100%). Notice that CC (list#1) = Funds Center (list#2).


Any help would be greatly appreciated!!!

Hello,

I have the following:
2 sheets:
On the second one, I have an table-range "name_range" (from where the data will be collected) on
the first sheet I have an table with header-row/header-column were the data will be put.
I would like to know how can I do an selection from the table-range (were the date stays) if value from the header-row(front were the data will be put) is not the same, then to take the biggest next value.
ex: Table were the data stays (sheet2)
15 20 30 40 50 ....1100
Table were the date will put (sheet1)
10 12 15 18 20 30 40 50 ....1100

if, from the table value 10 is selected and if on the table-data das not exist this value on the header to take the first biggest value in my case 10(sheet1) 15 (data)

I am using index(name_range, match(),match())

on match, MATCH(lookup_value, lookup_array, on [match_type]) I do not have the option that it is necessary for me

an the code will be like this:
Code:

index(name_range, match(r" & rTopLeft.Row & "c, name_range.first.row, 1), match (rc" & rTopLeft.Column, name_range.first.column,1))

thank you

Hi,

I have a problem, attached spreasheet lays it out with explanation based on this attachment below...

Basically in the section with the blue border i need to enter the date of a press advert (in yellow cell) which will output the day of the week in the green cell. Take row 3 as an example in the attached: So when i put in that an advert was in the Times i can add the date it went out and therefore what day it was on, in this case 6th Feb, Wednesday.

In the section with the black border in the attached i have a reference table that shows the % of calls that will result from an advert based on which day it was placed, for the following 11 days. For example in row 12 in this table, it shows that an ad placed on a sunday will generate 0% of its calls on that 1st day, 50% on the 2nd day, 20% on the 3rd day and so on..

What i need to happen is that in the red section of the attached, the % of calls generated from the reference table will be automatically inserted based on the date of the advert i have entered in the yellow cells..

So if i change the date of an advert it will automatically populate the red bordered section for that row, with the relevant % of calls per day, based on it knowing what day 1 is (from the date i have entered) and therefore matches this against the reference table and then knows to move onto day 2 calls the following day etc.

It should make sense if you read this whilst looking at the attachment!

I believe i need some index/match formula wizardry but i am not advanced enought to do that and i do not want the solution in VBA if possible.

Thanks in advance for any help.

I am trying to populate columns on sheet1 from a second worksheet(sheet2) using the INDEX() and MATCH() combination in a formula. The source is from a listbox populated from a named range on another sheet(sheet3).

For example:

A2 would be the list box selected and would say "Monday" I want B2 to populate from a second sheet by finding the reference against Monday on that sheet.

So far I have tried the following with out success

=index($a$2:$a$8(Match(sheet2!$b$2:$b$8,0),1)

Sheet2 would typically be:

A2 B2
Monday One
Tuesday Two
Wednesday Three
Thursday Four

and so on.

Any ideas where I'm going wrong out there?????

Ok here's what I am trying to accomplish.

I have a workbook with 3 sheets.

Sheet 1 is like this:
Code:

Airline_Name	Flight_No	Flight_Time	City
US Airways	2945	         1:57 AM	 LAS VEGAS

Sheet 2 is client data and I would like to input into a cell the Flight_No from Sheet 1 and have the next cells auto fill the info for Airline_Name, Flight_Time and City. This will done for arrivals and departures.

I've seen some formulas and code to auto fill in cells but nothing that quite matches what I'm trying to accomplish. Is this to big of a task for Excel?

I've attached the file for anyone that wants to see what it's like so far.

Good Morning All,

I have read some peoples suggestions as to how to lookup a value based on
two or more criteria using a combination of INDEX and MATCH fuctions. I have
tried to repicate this myself however my fuctions always return a #NA error.
Below is the data and function as it appears normally and under that is the
same range, showing the formula. I am try to match a model and a serial and
return the result (WO or R). I can't see why this formula isn't working, as
far as I've seen its should work on a combination of letters and numbers. If
anyone can see how to correct this could you please let me know.

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509
=INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
AOT24RZAL T005294 R AOT24RZAL T006183
=INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
AOT24RZAL T007509 WO AOT24RZAL
T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))

Does anyone know how to replicate Excel's Match function and Index function in VBA?

This is driving me crazy. Any help would be much appreciated!

I have 2 columns of data that looks something like this.
A B
sd 1
sd 2
xd 3
xd 4
sd 5
sd 6
xd 7

On another sheet I need a formula that will search column A for the first xd then give the number in column B. Then the next cell down, repeat the search for the next occurence of xd and return the value from column B. Etc...

Any ideas.

Thanks,

Mack

My initial spreadsheet contains a table of information, I am trying to create
a new spreadsheet with a cleaner version of the information. I am trying to
use the INDEX and MATCH functions; however, to return my values I must
consider three constants: customer, product, and date. So far I have only
been able to find a way to use two constants. All three must be considered
because many customers purchase a variety of products. The date is
horizontal while customer and product are both vertical.