Lookups With MATCH() and INDEX() Functions

Hello,

I've been doing vlookups for years, but now need a little more power with an Index and Match formula, as I need to compare text in one field list with text in another, and if there is a match, return a different column of data. So here's my example

Table 1
ID Name Version
1 This is first name v1
2 This is second name v4

Table 2
ID Name Version Lookup
1 This is third name Index-Match goes here to find version from Table 1 (no match, nothing)
2 This is first name Index-Match goes here to find version from Table 1 (should be v1)

The attached spreadsheet has the real example on it, for reference, or if this doesn't make sense.

Thanks!

hi,

I have a spread sheet with lots of rows of data.
and 5 columns. Column 1 is the "names".

I am trying to input a formula into same workbook - separate sheets whereby when it finds the name on the main sheet the entire row gets brought over into the same named sheet..

Does this make sense?
I have attached a sample.
http://www.4shared.com/document/bd11...mple_book.html

There are blank lines in the worksheet which signify new days..

Please check out - any help with this file would be terrific

I am working on a quite complex 5 team, 9 operator continuous shift
system using Excel 2002.

I have a sheet which contains 5 tables which are duplicating
information from 5 other sheets. Each table (9 cols / 366 rows) has a
row which corresponds to the day shift on the same date,
e.g. B4:J4, U4:AC4, AN4:AV4, BG4:BO4, & BZ:CH
only 1 cell out of the 5 ranges has an entry (could be any), all the
other cells are empty (although they contain formulas).
I am trying to create a single column of this entry. This will tell me
out of the 45 opertors who is on shift on each day.

I've been struggling with this for a couple of days, hope someone can
help

I've tried to attach a small sample

Book1.zip

myheadhurts

Experts needed. Can you help?

I want users to be able to enter two values: Age (in A1) and Score (in A2).

Then, I have an array/table with static values that serves as a reference table going from A4:F9

Age (column A, rows 4:9)
20
30
40
50
60

Rank (row 4, columns B:F)
Excellent -- Good -- Average -- Below Avg -- Poor

Then there are corresponding static values within that table array.

So it will look something like:

Age Excellent Good Average Below Avg Poor
20 100 90 80 70 60
30 95 86 76 67 57
40 90 81 72 63 54
50 86 77 69 60 51
60 81 73 65 57 49


Question: What I want to be able to do (using formulas, not VBA) is to return the appropriate label (eg, Average) depending on the Age of the contestant and the score they entered. The Age should be based on FLOOR(age,10) and the score should return the label based on the implied range of scores for that label.

So the user enters "31" for their age in A1, and a score of 77 (let's say) in A2, and the formula returns the appropriate label such as "Average."

I think I could do this using INDEX, MATCH, and INDIRECT, but I don't like using INDIRECT since it tends to break if you insert/delete rows. Can you help?!? Thanks for any ideas!

-JavaJoe

Hi Guys,

Would it be possible to combine INDEX and MATCH with range LOOKUPs?

I've attached an example file.

Thanks a lot.!

Hi,

I have a table like this:

Question response Employee ID Name Willson David 91100738 City, State and ZIP Code San Rafael, CA, 94901 91100738 Gender Male 91100738 Date of Birth 3/4/1977 91100738 Name Willson Mary 91100456 City, State and ZIP Code San Rafael, CA, 94113 91100456 Gender Female 91100456 Date of Birth 2/1/1972 91100456


I need to convert it into a table like this (one line per emaployee):

Employee ID Name City, State and ZIP Code Gender Date of Birth 91100738 Willson David San Rafael, CA, 94901 Male 3/4/1977 91100456 Willson Mary San Rafael, CA, 94113 Female 2/1/1972

Which function do I use to accomplish this?

Thank you excel gurus!

Hi Excel Masters =)!..

I'm just new here and desperately need of your help. This is about daily, weekly and monthly product sales stats. The dates are listed in the columns and products in rows. I just want to have a weekly and monthly summary report (SUM) looking up the products + dates (lookup criteria) on a separate sheet. Is it possible to make the dates and products as my conditional criteria considering they're on a different axis?

Example:

Raw data:

Jan-1 Jan-2 Jan-3 Jan-4 Jan-5 Jan-6

Product 1
Product 2
Product 3
Product 4
Product 5

*I can actually TAG the dates with week titles just to make it easier for the lookup / index match formula.

Like this:
Week1 Week1 Week1 Week1 Week1 Week1
Jan-1 Jan-2 Jan-3 Jan-4 Jan-5 Jan-6

Product 1
Product 2
Product 3
Product 4
Product 5

Results Desired on a separate sheet:

JAN JAN JAN
Week1 Week2 Week3

Product 2 SUM SUM SUM
Product 3 SUM SUM SUM

Then extended to monthly summary on another sheet:

JAN FEB MAR


Product 2 SUM SUM SUM
Product 3 SUM SUM SUM

*The tagging will actually allow me to insert new columns without including the data into the summary.

Is it possible?.. Pls.. Help.. THanks! =)

hi all, not posted on here for some time but have a small issue I am trying to deal with

I am trying to create a function we

if the values in columns A & B (sheet 1) are the same as the values columns A & B (sheet 2) return MATCH, if not return NO MATCH

hope you can help, thanks in advance

Phil

My Dear Excel Gurus:

I am using the following formula in my spreadsheet to look up values from a 2x93 range:

INDEX(P1:Q93,MATCH(H347,P1:P93,0),2)

I need to modify the formula to, instead of matching the contents of H347, match the contents of column H and the current row number.

Can you please tell me how to modify the H347 entry to do this?

Your help will be greatly appreciated.

Ansonicus

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