Multi input lookup?

[Jersey417]

Hey guys,
I'm sure this issues has been discussed before but I'm not really sure what to look for. I have a large file of 6000+ rows and need to be able to transpose the data for the use that I need. I attached a file with a small section of the data that I'll be working with

I have three columns. The first two I need to use as the inputs and the third is result.

Base Example Example.
Model+componentDesc=component

In use example
5SME39CQLB001+PM MAGNET=24A103280-P02

I'll have 800+ model numbers and 65 different componentDesc. Sorry for how simple this is... I've just never had to use this information before.

[teylyn]

5SME39CQLB001+PM MAGNET=24A103280-P02

What is the logic here? Can you describe why adding the first to results in the third? I see no connection, really.

[davesexcel]

Welcome to the Forum,

Your example needs more clarification on what you desired results should be.

[Jersey417]

Sorry,
I'm not really adding them. I just didn't have another way of explaining it.

[teylyn]

I think I see what you mean now, although you don't describe it. You want to lookup the component based on the user entering Model and componentDesc.

With F3 holding Model and G3 holding componentDesc, you can retrieve the component with this formula

=INDEX(C:C,MATCH(F3&G3,INDEX(A:A&B:B,0),0))

[Jersey417]

teylyn,
You're exactly right. Again, sorry for my ignorance in being able to describe my issue, this isn't my field of expertise.

I entered the formula that you mentioned and I’m not getting component result that I’m looking for. Am I entering something incorrectly?

I attached two files. The first titled “simple example” is the example I posted with the initial post with the formula and the #NUM! result.

The second file titled “building material level one(small)” is the file I’ll be using for work with all my data. I’ll be entering the formula in cell B3 which will reference the model from A3 and componentDesc from B2. The array will be located in the DATA sheet.

Any help is greatly appreciated

[Jersey417]

Second file won't upload..."Database error" I'll try again later.

[teylyn]

If you use this formula across sheets, i.e. with the formula in one sheet and the table in another, you need to assign range names to the data in columns A and B. Two range names

model =Data!$A$1:$A$1000
componentDesc =Data!$B$1:$B$1000

then use something like

=INDEX(Data!$C$1:$C$1000,MATCH(F3&G3,INDEX(model&componentDesc,0),0))

where F3 and G3 are in the same sheet as the formula and are the cells where you enter model number and description.