Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookout and if
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookout and if
Maybe
=IF(ISNA(VLOOKUP(A3,$D$3:$E$20,2,0)),"",CHOOSE((B3 =VLOOKUP(A3,$D$3:$E$20,2,0))+1,"no","yes")) " wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookout and if
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookout and if
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))) "JMB" wrote: Maybe =IF(ISNA(VLOOKUP(A3,$D$3:$E$20,2,0)),"",CHOOSE((B3 =VLOOKUP(A3,$D$3:$E$20,2,0))+1,"no","yes")) " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|