Multiple Lookup Values rows and columns to lookup a single target column on the right end.

Hello,

I have a table of data (say Column1 to Column 5) with multiple rows.

Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.

I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet.
I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.

It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.

I have attached an example for your reference. Please help me. Thanks.

Try this formula

=SUMPRODUCT(($A$2:$D$6=A13)*($E$2:$E$6))

@ gurusanthanam

Note: the values that you give in B15:B17 are incorrect so the formula given by AlKey will seem to be incorrect but is it CORRECT.