Vlookup (Lookup value in mutiple columns)

[vijay.vizzu]

Dear All..


I want to put a formula for my work that, assuem that the source data starts from A1:E200, now i will input data in G1,H1,I1,J1, now i wish to create to put a formula in K1 that if the input cells like G1=A1,H1=B1,I1=C1,J1=D1, if this became true then it extract data from E1. i have tried vlookup but lookup can't clear, lookup value should be four cells. so kindly help me

 

[Hui]

In G1 put

=If(and(G1=A1,H1=B1,I1=C1,J1=D1),E1,"")

copy down

 

[kchiba]

Hi Vijay


try this


=(A1=G1)*(B1=H1)*(C1=I1)*(J1=D1)*E1

 

[vijay.vizzu]

Thank you Hui, but i need it vlookup, bcoz above formula will work if the source data and input data will be the same (row wise), let me give you more explation, input1 will check A:A range and then output to be stored, then again Input2 will check in B:B the output to be stored like input3, input4, then final data pickup from E:E, i mean to say, the input may be somewhere in the column, so it should check first input1 in the column A, and then input2 in the column b.... like that,


Now i think it will be clear....


Thanks in advance.

 

[kchiba]

Sorry Vijay,


Not clear at all.

Are you saying that the data in columns G to J are coming from some other tables which are using column A to D as lookup values. So the data in G depends on the value returned by looking up the value in A etc.?

 

[vijay.vizzu]

Dear Kchiba,


Thank for your reply. I will manually input in G:J, output will be displayed in another column. if i will input aaa in G1, it should check in A:A and save it internally like other column also. finally matching data should be displayed.

 

[Faseeh]

Hi vijay.vizzu,


Assuming that you are entering criteria columns G to J that will match respectively with A to D and will fetch corresponding value from E, the following formula should work:


=INDEX(E1:E21,SUMPRODUCT((F1:F21=A1)*(G1:G21=B1)*(H1:H21=C1)*(I1:I21=D1)*ROW(I1:I21)),0)


The sumproduct() part will check for your given condition G1=A1,H1=B1,I1=C1,J1=D1 and fetch a row number, index() will pull the corresponding data from that row from column E.


Regards,

Faseeh