Look up multiple values from table and return another value

**ajw273** · 02-02-2010, 09:00 PM

I am working on a workbook at work. It is being created to log in documents that need to be approved for construction. The problem I am having is that sometimes these documents come back as rejected and we have to create a revision. Currently my file looks like this:

Row 1: Item #, Revision #, Release #, Equipment Description
Row 2: M-01, 0, 1, Thing #1
Row 3: M-02, 0, 0, Thing #2
Row 4: M-02, 1, 1, Thing #2 Revised
Row 5: M-02, 1, 2, Thing #2 Revised

There are many more columns with information and many more rows of document records that continue the same way. What I am trying to do is use a VLOOKUP or something of the sort on another worksheet so that when someone types M-02 for Item #, 1 for Revision #, and 2 for Release #, all of the other information will just fill into other cells so they will be able to see the whole record on one page. I'm having trouble with the VLOOKUP because there are multiple instances of each value in each column.

Can anyone give me some insight on how I can fix this problem?

**Palmetto** · 02-02-2010, 09:12 PM

What I am trying to do is use a VLOOKUP or something of the sort on another worksheet so that when someone types M-02 for Item #, 1 for Revision #, and 2 for Release #, all of the other information will just fill into other cells so they will be able to see the whole record on one page.

See if the attached is of help. This VLOOKUP formula uses three criteria.

=VLOOKUP($A2&$B2&$C2,Sheet1!$A$2:$F$5,4)

**Paul** · 02-02-2010, 09:13 PM

Hi AJW, welcome to the forum. Please see the attached sheet for a possible solution. I created a merge column of the Item/Revision/Release on Sheet1 (column J) to perform a non-array lookup from Sheet2 columns D:H.

=INDEX(Sheet1!D:D,MATCH($A2&$B2&$C2,Sheet1!$J:$J,0))