Excel Lookup/Search Tip 1 - Using Index and Match - It's Better than Vlookup

Okay, I've got an Excel file that is being Beta tested by a few hundred users right now. The plan is to roll this out to a larger audience, but there's a problem we've run into. The file is still usable, but it feels like it involves an extra step.

It's difficult for me to explain the file so I was hoping to get a few Excel pros working on it. Removed by moderator
Here's a video tutorial on the file:
http://www.youtube.com/watch?v=ybHZ9DSIxuE

Basically the problem is this: In the chart at the very top of the Excel file, certain names are being drawn from the 4 lists of names at the bottom. What we'd like to have happen is for the names at the top auto-fill in the top-ranked name based on the labeled columns "FPts" and "ADR".

Removed by Moderator

Hello,

Would anyone be able to help me figure out a formula that I am stumped on? Basically, I've got a table of data that has Names, Total # Errors (for each name), and Categories (this is the "type" of error for each error listed).

My job is to find the Quality % for each person and for each Category. Which means, for each name, I have 1.) find out how many (the "count" - not sum) of Total Errors they have, and 2.) find out how many of those errors were made in which Category. From there, I can do a simple formula to calculate their Quality % for each Category. The challenging part, for me, is finding out what formula I need to use to find the 1. and 2. listed above.

Can anyone help to figure out a formula for me to use? Here's a sample of the table that I'm working on...

http://tinypic.com/r/j5jfv5/7

Please let me know if you can help!
Thanks SO much!!
Gina

Hello,

I have a spreadsheet (sample attached) with four columns. One is a long list of client names, and in the adjacent column is a list of their corresponding firms. In the third column I have a list of unique client names (since the client list has many repeats). I would like the fourth column to display the firms that correspond to the unique client names.

I have tried fiddling around with VLOOKUP and INDEX and MATCH functions, but to no avail.

Any advice would be much appreciated!

Thanks,

Conor

Hi all,

I have one list of names Column A and an other key table with some names Column E (not all of the ones in the first list AND partial match) and a type of relation next to it Column F (see below).

Now I want to display in Column B the type of relation from Column F matching name of Column E


Column A Column B
Pierre Friend

Hi, I'm wrecking my brains tying to figure out how to set a vlookup based on two inputs.
Input one "a1" is product model, input 2 "b1"is the country its destined for.
The output is whether we have the translated language for such particular product.
If we don't have it translated, the cell remains blank.

The range with all the countries is called "Countries" they are in column C6:C26 & the range with the products is called "product.range" & these are in the row f4:N4 & the entire table is in a range simply called table c4:n26

My first attempt was to use vlookup(b1,table,match(a1,Product.range,0)false & the other was index(table,Match(a1,product.range),Match(B1,countries)).

Both formulas give the same results & if the language is not translated, they give the nearest answer which is wrong. I'd rather if it'd return "English" as a default.

Can anyone help with this.
I know it's probably a simple thing I'm leaving out but it has me clueless.

Any help at all would be greatly appreciated.
Thanks.

Hello,

I have this table

.......A.....B.....C....D
1..... I......a.....d.....g

2 .....II.....b.....e.....h

3 .....III....c.....f......i

As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i

I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on

I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately if possible


Thank you

Column N contains dates:

Column Q contains numbers:

Formula should give me the date in Column N that has the first positive number in Column Q.

This is the formula I have used:

=INDEX('Analysis Report'!N4:Q123,MATCH(TRUE,'Analysis Report'!Q4:Q123>0,0)) (used as an array formula)


Correct Formula should be =INDEX('Analysis Report'!N4:N123,MATCH(TRUE,'Analysis Report'!Q4:Q123>0,0)) (used as an array formula)

I have 2 worksheets. One contains account number, payment and payment date. If there are multiple payments I have multiple rows of payments for the same account number.
The other worksheet has the same account numbers with payment codes based on a start and end range of dates.
I want to look up the account number and based on where the payment date (in worksheet 1) falls in the date range (worksheet 2), bring over the payment code from worksheet 2.

OK here is my problem: (I want to solve it without macros)


I have a Sheet (1) with the column titles as "Name"; "Week number"; "Completed"; and other irrelevant headings.


On another sheet (2) I have a persons name going down, and the week number going across. In this I want to fill either true or false, which depends on what the "Completed" column in the other sheet (1) says.


The problem I'm having is that I can for example look the persons name up, but not at the same time as the week number.


Any Suggestions?