Ask Experts Questions for FREE Help !
Ask
    kvinay_00's Avatar
    kvinay_00 Posts: 36, Reputation: 1
    Junior Member
     
    #1

    Jun 15, 2009, 01:51 AM
    query in data search
    I have a data as below (see attached sample) where I want to apply a formula (something like vlookup) which will give me the cell value based on the row and model code (A,B or C).

    If I put labour code and model, I should get the correct value.

    Labour Description Models
    Code A B C
    11111 change cable 5.2 1.2 6.3
    22222 change filter 2.5 3.6 0.6
    33333 change nut 8.1 2.1 0.4
    44444 remove gasket 6.4 1.9 2.9
    55555 replace assy 7.3 9.6 8.9
    66666 remove pipe 9.1 6.5 7.5
    77777 change ring 0.6 5.8 6.4
    88888 replace handle 0.8 4.8 4.3

    I tried, but could not get any solution.

    Can anybody help please?

    Thanks in advance.
    Attached Files
  1. File Type: xls Test file.xls (20.0 KB, 158 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Jun 15, 2009, 01:12 PM

    Code choice in G3
    Model choice in H3
    Answer in I3: =INDEX($C$3:$E$10, MATCH($G3, $A$3:$A$10, 0), MATCH($H3, $C$2:$E$2, 0))

    I then made "Named Ranges" out of the colored sections to make the formula easier to read. The new formula would read like this:

    =INDEX(Table, MATCH($G5, Codes, 0), MATCH($H5, Models, 0))
    Attached Files
  3. File Type: xls IndexMatchSimple.xls (14.5 KB, 151 views)
  4. kvinay_00's Avatar
    kvinay_00 Posts: 36, Reputation: 1
    Junior Member
     
    #3

    Jun 16, 2009, 09:00 PM

    Thank you JBeaucaire !

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Collect data into 2 combo boxes from data in an access table [ 1 Answers ]

I have two combo boxes. When the form loads the db connection opens a recordset that selects all the data from the retaillocations table. When you choose the region (0-4), that number loads into a text box and the Contacts load into the forst combo box. Now I want to select only the Name based on...

Search for visitor,if exists,use data,else,enter? [ 1 Answers ]

When visitor comes,I enter their ID,if he/she exists,I enter a new record for her visit using the details already in the database. But if he/she does not exist in the database,want to be able to enter his/her details in the visitor s details form and then after that,enter the details of his or...

Query pass in vb data report [ 2 Answers ]

I have three records in a table with staff no 555,666,777 and I want to pass select query in data report for only staff no =666 please solve this problem


View more questions Search