PDA

View Full Version : Query in data search


kvinay_00
Jun 15, 2009, 01:51 AM
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.

JBeaucaire
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))

kvinay_00
Jun 16, 2009, 09:00 PM
Thank you JBeaucaire !