Looking up multiple values and returning one corresponding value

[Nightrain]

I am wondering if it is possible to lookup multiple values using the vlookup
function and return one corresponding value? More specifically, I want to
lookup a part number and machine number and then return a value associated
with each of the two criteria. I have tried using nested vlookup functions,
but have yet to get anything to work properly. Any ideas? Thanks in advance.

 

[Duke Carey]

You can use an array formula, which is entered by pressing Ctrl-Shift-Enter

Assuming the:
- part# & machine# are in E1 and F1, and
- part# and machine# in your table are in columns A and B,
respectively, and
- the value you want to get back is in column C
use:

=INDEX(C1:C3,MATCH(E1&F1,A1:A3&B1:B3,0))

be sure to enter it correctly

 

[Flcnmech]

Okay, attempted to follow this but it just isn't working for me. Here is
what I have. On sheet 1 is all my data (A2 through I300). sheet 2 is where
I want to grab and post in a table. Column A is all the serial number, Row 1
is all the inspections. I want this to be able to take the value in A2 find
it and match its inspection in B1 with the time remaining on Sheet 1.

Sheet 1 columns are set up like this:

S/N Equip # Status Insp Total time Due time Time rem. Date
Due forecast usage

then the rows are:

1 a good a 32156.0 55500.0
23344.0 aug 5, 2020 12 years

Thanks ahead of time.

 

[dan dungan]

I'm not clear.

Okay, attempted to follow this but it just isn't working for me

How specifically did you attempt to follow this?
How do you know it's not working?
What formula did you use?

Column A is all the serial number, Row 1
is all the inspections.

Is this on sheet 1 or 2?

I want this to be able to take the value in A2 find

On sheet 1 or 2?

and match its inspection in B1 with the time remaining on Sheet 1.

Inspection seems like a row. How is its inspection ever going to match
the time remaining?

 

[Flcnmech]

Added data... This is what I created from yours.

=INDEX(Insp!A2:I300,MATCH('Data Sheet'!A2&'Data
Sheet'!B1,Insp!A2:A300&Insp!D2300,0))

and

=INDEX(Insp!$A$2:$I$300,MATCH('Data Sheet'!$A$2&'Data
Sheet'!$B$1,Insp!$A$2:A300&Insp!$D$2300,0))

Both return - #VALUE! error instead of the value in the block associated
with that serial number and inspection.

 

[Duke Carey]

Substitute your own sheet names, and increase the size of your ranges beyond
rows 1:4

=INDEX(Data!G1:G4,MATCH(Results!A2&Results!B1,Data!A1:A4&Data!B1:B4,0))

BE SURE TO ENTER IT AS AN ARRAY FORMULA (Ctrl-Shift-Enter)

 

[Flcnmech]

Sheet 2 has the list of serial numbers in Column A
Sheet 2 has the list of inspections in Row 1

I want to fill in the inspections matching the data in Sheet 2 Column A and
Row 1 into Sheet 2 from the table in sheet 1. Sheet 1 has each row as a
separate inspection, the column headers are the type of data. (ex: Serial
Number, Inspection, Time Due, Time Remaining...)

I also posted the actual algorithms I built from the first example.

 

[Flcnmech]

I guess I am not following the enter it as an Array Formula
(Ctrl-Shift-Enter) I am typing the equation into the cell. When I have the
cell active and hit the keystroke nothing happens.

 

[Duke Carey]

=INDEX(Insp!A2:I300,MATCH('Data Sheet'!A2&'Data
Sheet'!B1,Insp!A2:A300&Insp!D2300,0))

OK, first part that you have as =INDEX(Insp!A2:I300

should be

=INDEX(Insp!G2:G300

since that is the column with the time remaining, right?

Next, the MATCH() portion - you said your Insp sheet had the S/N and Equip #
in columns A & B. If that is so, then that part of the formula should be

MATCH('Data Sheet'!A2&'Data Sheet'!B1,Insp!A2:A300&Insp!B2:B300,0)

If I've interpreted the column incorrectly, what you want to do is enter the
columns that contain the data you are looking up, in the same order that they
appear in the first part of the formula. So..if Datasheet A corresponds to
Insp A and Datasheet B corresponds to Insp C, you use

MATCH('Data Sheet'!A2&'Data Sheet'!B1,Insp!A2:A300&Insp!C2:C300,0)

 

[Spiky]

I guess I am not following the enter it as an Array Formula
(Ctrl-Shift-Enter) I am typing the equation into the cell. When I have the
cell active and hit the keystroke nothing happens.

When you are done typing the formula, or pasting it, hold down CTRL
and SHIFT, then press Enter. Usually you just press Enter, which isn't
enough in this case. You can tell the difference because Excel will
put braces {} around the entire formula, like this:

{=INDEX(Data!G1:G4,MATCH(Results!A2&Results!B1,Data!A1:A4&Data!
B1:B4,0))}

And by "I have the cell active", I'm guessing you mean you have it
"selected" if it hasn't worked so far. You actually have to be in Edit
mode, where there is a cursor blinking in the formula bar.

 

[Flcnmech]

Thanks to all of you... I got it working... now just need to copy it to all
the other cells. Thanks again.