Help in Excel 2007!! (Comaprision )Closed

Question

Hello,

  i need the value of a cell by comparing another two cells in a list 

  like vlookup
   but in v lookup it compares single cell i need 2 cell .

ex:

  i am having a  list material codes and for each material code more than 2 suppliers are there
 each will supply material in different dates . 
  i need the corresponding receipt date by comparing material code & supplier code for a list of material codes.
in vlookup v have the option compare one filed. but i need 4 2 fields.

Any body can help me ...

venkat1926 · Answer

I am not giving you a solution because I do not know your sheets. But I am telling you here how to go about it (famous adage: do not give fish but teach him fishing)

It is possible to do vlookup for two values 
do this experiment 

type in shseet1 from A1 C3 following data 

a	s	venkat
a	d	ram
a	f	well

go to sheet 2 
A1 ---a
B1 ---d
in C1 copy this formula 

=INDEX(Sheet1!C1:C3,MATCH(1,((Sheet1!A1:A3=A1)*(Sheet1!B1:B3=B1)),1))

INVOKE THIS FORMULA WITH CONTROL+SHIFT+ENTER  as this is an array formula

you will get in  C1 the value "ram"

this may give you idea how to go about it.

venkat1926 · Answer

your sheet1 is like this from A1 to c8

a	1	ram
a	2	ravi
a	4	anand
a	5	hi
c	9	hello
c	0	v1
b	5	vyu
d	5	du

your sheet 2 is like this  from A1 to B4

a	5
a	1
b	5
c	0

in sheet 2 in C1 copy this formula ( I have slightly changed the macro for dynamic conditions)

=INDEX(Sheet1!$C$1:$C$100,MATCH(1,((Sheet1!$A$1:$A$100=A1)*(Sheet1!$B$1:$B$100=B1)),0))
not dollar signs (maximum of 100 rows;. if more modify )

INVOKE THIS FORMULA BY CONTROL+SHIFT+ENTER
copy C1 to C2,C3 down. 

you will get  sheet 2 like this 

a	5	hi
a	1	ram
b	5	vyu
c	0	v1

now do you get the hang of it.

venkat1926 · Answer

I do not know whether you have solved the problem

this has nothing to do with random or non random order. how did you get A  2  as not available (#N/A).
in my computer it is --------------dd

of course my version is excel 2002. still it should work in a future version.  see whetehr there are any typo in sheet 2
when you type A and 2 are there any extra space. delete A and 2 and retype again. and get the formula.

Help in Excel 2007!! (Comaprision )

3 responses

Similar discussions

Subscribe To Our Newsletter!