Lookup formulas

Hello Community,
I would greatly appreciate if some one could give me advice on how to fix a formula that I have written.

I am trying to write a lookup formula that searches data on a table on “sheet2” using two different parameters. I want to display the matched values found on the table on sheet 2 in “sheet1”. From online research, I have created the following function,

Formula:

Please Login or Register  to view this content.

Where:
j4 &”I”&k4 = Cells where you would type the two different parameters in “sheet1”.
A4:d83 = Table where the data is located on “sheet2”
4 = The column on “sheet2” that contains the data to be displayed if matched on “sheet1”
The problem is that I need to “tell” excel to search sheet2 for the two different parameters typed on sheet1. I would also like to display the matched value on sheet1. Any other suggestions would also be greatly appreciated.

Thank you very much for the help.

If there are duplications in the lookup columns and or potentially multiple results, then this would effect how it would be possible to do this.

Array formula maybe be best, this would work based on what you described:

Formula:

Please Login or Register  to view this content.

Hold down left control and left shift while pressing return to enter it as an array formula, it should then have {} around it as in my example to indicate that it has been entered as an array formula.

You can then copy and paste it down a number of rows in order to list all matching results if the are more than 1 match.

Hope it helps

Hello D.Lovell,
Thank you very much for the help once again.

Excuse my inexperience, but for some reason when I plugged the formula in it does not seem to workPrices.xlsx. Attached is is what I did.

Thank You once again for your time and attention.

You can also do it like this as an array formula: (Hold down left control and left shift and press enter)

Formula:

Please Login or Register  to view this content.

Originally Posted by AlKey

Try this

=SUMIFS(Sheet2!D$4:D$10,Sheet2!B$4:B$10,J4,Sheet2!C$4:C$10,K4)

Thank you very much for the response!

Thank you for your time and attention.

I will give your method a try!