Lookup two different columns

[Guest]

Sheet 1:
LINE LOCATION
1 2
4 1
2 3
3 1

Sheet 2:
LINE LOCATION ORDER#
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
4 1 9

I would like to look for the row that has a match for Sheet 1 Line AND
Location in Sheet 2 and return the value in Column 3 from Sheet 2. So for
the first row in Sheet 1, I would return 2; second row would be 9, etc. Can
someone tell me how to combine all the different lookup functions to
accomplish this?

Thank you very much!

 

[Guest]

=INDEX(Order_range_sheet2,MATCH(1,(Line_range_sheet2=A2)*(Location_range_sheet2=B2),0))

entered with ctrl + shift & enter



Regards,


Peo Sjoblom

 

[Guest]

After the word MATCH, what is the 1 for? It's not working for me and I think
I understand everything but that, so I thought that might give me the answer.
Thanks for your help!!

 

[Guest]

The formula is doing a logical test and multiplying that against another
logical test (of an array range.) So, for each row in the range, the first
test is going to either be true or false and likewise the 2nd test will
either be true or false. When you multiply true*true you get a 1 (which is
what the match is testing) Any thing else will result in 0 (true*false,
False*true, and false*false)

 

[Guest]

I keep getting N/A for the answer, but I can't figure out why. Here is my
formula.

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=Sheet1!A2)*(Sheet2!B1:B10=Sheet1!B2),0))

 

[Kevin Vaughn]

I keep getting N/A for the answer, but I can't figure out why. Here is
my
formula.

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=Sheet1!A2)*(Sheet2!B1:B10=Sheet1!B2),0))

Try some formulas to see which part is failing. It appears that you
expect that somewhere on rows 1 through 10 fo sheet 2, both a cell in A
is going to match what is in A2 of sheet 1 and a cell in B (of the same
row) is going to match what is in B2 of sheet 1. So try 2 different
formulas. Let's say that the row that you think should match both A
and B is row 2. Try a formula that tests this. Like a formula in
sheet1:
=A2=Sheet2!A2 If this does not return true, there is your problem.
Check for things like trailing spaces.
and =B2=Sheet2!B2 should also return true. Also, as I'm sure was
mentioned in the original post as this is an array formula it MUST be
entered using Ctrl-Shift-Enter, not just Enter.

 

[Guest]

I think I've got it now. I was using Ctrl-Shift-Enter, but I thought you
were supposed to start with that instead of ending with it. It's not very
clear in the help file (or maybe I'm just a dope!!) I think it works now.
Thanks for your help!!

 

[Guest]

OK, one more question (I hope). If the items in my columns are text instead
of numbers, does that make a difference? So, my Line column is Air Piping,
Frame, Main, etc? It was working, but now I'm trying to use it for a text
version of the same thing and it is coming back with N/A again. GRRRRR!

 

[Guest]

Text should work (just look out for trailing spaces etc.)

 

[Guest]

You are very correct. I found out the reason for my N/A's this time was a
leading space in one of the columns.

Thanks again for all your help!!

 

[Guest]

You're welcome! Glad you got it working.