vlookout and if

[tomaz.vouk]

I am basically having 2 times 2 columns of data that i have to compare
and find if it matches (i am comparing existing inventory with the
inventory we would most urgently need):

it would look approximately like this:

1 2 3 4 5
Part# quantity Part# quantity
V90mdm 3 vxcvxc 1
Gmcrd 7 CDROM 0
Opmse 7 CDRW 1
Wlopm 3 dfdf 3
Ethernt 8 dsa 0
CDROM 2 DVDRW 2
CDRW 1 Ethernt 8
DVDRW 2 fasd 0
Th128 4 Gmcrd 4
Th256 6 Opmse 0
sag 3
Th128 4
Th256 6
V90mdm 1
vcx 7
vcxz 7
vxcz 0
Wlopm 0

where I would have to find out in column3 if the column 1(part#) and
column 2(part quantity) exactly match with the columns 4 and 5. column3
would have to display answers like yes (enough stock) or no(not enough
stock) or even display the number of missing parts. however it might
also happen that there is no part in column4 at all.

I have already tried with various formulas including if and isna and
vlookup but couldn't make it work for 2 criteria together. i only
managed to find out the match from column 1 and column 4 and display it
as yes or no in column3.

does anyone have any suggestions, please?

Thx.

Tom

 

[Guest]

Maybe
=IF(ISNA(VLOOKUP(A3,$D$3:$E$20,2,0)),"",CHOOSE((B3=VLOOKUP(A3,$D$3:$E$20,2,0))+1,"no","yes"))

 

[Franz Verga]

I am basically having 2 times 2 columns of data that i have to compare
and find if it matches (i am comparing existing inventory with the
inventory we would most urgently need):

it would look approximately like this:

1 2 3 4 5
Part# quantity Part# quantity
V90mdm 3 vxcvxc 1
Gmcrd 7 CDROM 0
Opmse 7 CDRW 1
Wlopm 3 dfdf 3
Ethernt 8 dsa 0
CDROM 2 DVDRW 2
CDRW 1 Ethernt 8
DVDRW 2 fasd 0
Th128 4 Gmcrd 4
Th256 6 Opmse 0
sag 3
Th128 4
Th256 6
V90mdm 1
vcx 7
vcxz 7
vxcz 0
Wlopm 0

where I would have to find out in column3 if the column 1(part#) and
column 2(part quantity) exactly match with the columns 4 and 5.
column3 would have to display answers like yes (enough stock) or
no(not enough stock) or even display the number of missing parts.
however it might also happen that there is no part in column4 at all.

I have already tried with various formulas including if and isna and
vlookup but couldn't make it work for 2 criteria together. i only
managed to find out the match from column 1 and column 4 and display
it as yes or no in column3.

does anyone have any suggestions, please?

Thx.

Tom

Hi Tom,

try this formula in C2 then copy down:

=IF(ISNA(VLOOKUP(D2,$A$2:$B$19,2,0)),"",IF(B2-VLOOKUP(D2,$A$2:$B$19,2,0)>0,"OK",ABS(B2-VLOOKUP(D2,$A$2:$B$19,2,0))))

where $A$2:$B$19 are the columns with existing inventory (columns 1 and 2 in
your example), while D2:E11 are the columns with needed inventory (columns 4
and 5 in your example), and column C is where to check if existing inventory
has enough stock for your needs (your column 3).

Adjust references as for your conveniences.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

Hi Tom,

try this formula in C2 then copy down:

=IF(ISNA(VLOOKUP(D2,$A$2:$B$19,2,0)),"",IF(B2-VLOOKUP(D2,$A$2:$B$19,2,0)>0,"OK",ABS(B2-VLOOKUP(D2,$A$2:$B$19,2,0))))

where $A$2:$B$19 are the columns with existing inventory (columns 1
and 2 in your example), while D2:E11 are the columns with needed
inventory (columns 4 and 5 in your example), and column C is where to
check if existing inventory has enough stock for your needs (your
column 3).
Adjust references as for your conveniences.

Correction...

This one should be much more bette than my previous; type (or copy and past)
this formula in E2 then copy down:

=IF(ISNA(VLOOKUP(C2,$A$2:$B$19,2,0)),IF(D2>0,D2,"OK"),IF(VLOOKUP(C2,$A$2:$B$19,2,0)-D2>=0,"OK",ABS(VLOOKUP(C2,$A$2:$B$19,2,0)-D2)))

where $A$2:$B$19 are the columns with existing inventory (columns 1 and 2 in
your example), while C211 are the columns with needed inventory (columns 4
and 5 in your example), and column E is where to check existing inventory
vs. your needs (your column 3).

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Your original post said you wanted to check if the amounts were exactly
equal, but Franz's interpretation makes more sense to me (if you have enough
inventory to cover your needs, shouldn't the result be "yes"?) If the
parts/amounts needed is in A3:B12, the parts/amounts in inventory are in
D3:E20, then I would change my formula to this, put it in C3 and copy down:

=IF(ISNA(VLOOKUP(A3,$D$3:$E$20,2,0)),"",CHOOSE((B3<VLOOKUP(A3,$D$3:$E$20,2,0))+1,"no ","yes ")&ABS(B3-VLOOKUP(A3,$D$3:$E$20,2,0)))