Help in Excel 2007!! (Comaprision )

Closed
Report
-
 Jee -
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 ...

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803


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.
Hi venkat,

its not working.. i wnna do it for list of cells. it just give for single cell if v drag then it give irrelevant values.

i will give u ex.

sheet 1 sheet2

a 1 ram a 5 ...
a 2 ravi a 1 ...
a 4 anand b 5
a 5 hi c 0
c 9 hello
c 0 v1 list goes ..
b 5 vyu
d 5 du

list goes

i need the values in column c in sheet 2. ok
any way thanks...
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
HI VENKAT

Thanks ... its working.
but still some values are not matching give not available
if we gives in random order
i.e in sheet 1

A 1 ff
A 2 dd
D 3 ff
A 4 gg
B 3 hh
A 6 jj
C 1 rty
D 4 hjk
F 5 ghjk
F 6 fghj

second sheet

A 6 jj
D 3 ff
A 2 #N/A
A 1 ff

---
but its ok .. i finished my excel. my data are not in random manner
ok thanks ..
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
Hi Venkat
still i am getting not available values in some cells
give me ur mail id.. i will send my excel sheet
Hi Jee,

Just want to let you know that it's also possible to upload you file (without any personal info) using the folowing link:
https://authentification.site

Best regards,
Trowa
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803 > Trowa
thank you for the information. Suppose I upload the file will the member be able to see and copied it to their excel workbook.
>
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021

Thanks a lot ...
Venkat.. Have a honey hours